Spliting worksheet into separate files

[blkmagik]

Hello,

I currenty have a excel file which i run a macro to insert pagebreaks based on a specific criteria. Unfortunately the macro fails because there is a limit of 1000 pagebreaks. In most cases the file will require for me to split the worksheet manually into separate files for the macro to work.

I am trying to figure out a way to create a macro that will help me at least split the file for ever 1000th occurance of the word Merchandise.

Thinking it over i decided to insert a new column and use a formula that will mark the number 4 adjacent to the line that has merchandise in it.

Im trying to figure out where to go from there, is there a way for me to look at column A and have the macro count down to the 1000th occurance of the number 4 in COL A based on the formula im using and then select that row including everything below and then paste into a new work sheet? Repeat the process until i get clear back to the top?

The row vary in length and the detail between the first row and the end of the data group varies so i need to use the formula i added in column a

for_ch___872

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right">4</TD><TD>Merchandise was returned to:</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD>1) TECH </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">1</TD><TD>2) ATTENTION RETURNS DEPT.</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">1</TD><TD>3) SOUTHRIDGE </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">1</TD><TD>4) MEMPHIS TN 38141-8300</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">1</TD><TD>Carrier Information</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">1</TD><TD>Carrier:</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">1</TD><TD>Total Cases\Weight:</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">1</TD><TD>PRO#/Tracking#:</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">1</TD><TD>Misc. charge general:</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: right">1</TD><TD>Log Line#:</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: right">1</TD><TD>Comment:</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: right">1</TD><TD>Units Received</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">-1</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: right">1</TD><TD>********** END OF DEDUCTION NOTICE</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: right">4</TD><TD>Merchandise was returned to:</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD style="TEXT-ALIGN: right">1</TD><TD>1) TECH </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD style="TEXT-ALIGN: right">1</TD><TD>2) ATTENTION RETURNS DEPT.</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: right">1</TD><TD>3) SOUTHRIDGE </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: right">1</TD><TD>4) MEMPHIS TN 38141-8300</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</TD><TD style="TEXT-ALIGN: right">1</TD><TD>Carrier Information</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">27</TD><TD style="TEXT-ALIGN: right">1</TD><TD>Carrier:</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">28</TD><TD style="TEXT-ALIGN: right">1</TD><TD>Total Cases\Weight:</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">29</TD><TD style="TEXT-ALIGN: right">1</TD><TD>PRO#/Tracking#:</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">30</TD><TD style="TEXT-ALIGN: right">1</TD><TD>Misc. charge general:</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">31</TD><TD style="TEXT-ALIGN: right">1</TD><TD>Log Line#:</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">32</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">33</TD><TD style="TEXT-ALIGN: right">1</TD><TD>Comment:</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">34</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">35</TD><TD style="TEXT-ALIGN: right">1</TD><TD>Units Received</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">36</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">-1</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">37</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">38</TD><TD style="TEXT-ALIGN: right">1</TD><TD>********** END OF DEDUCTION NOTICE</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A1</TD><TD>=IF(LEFT(B1,7)="Merchan",4,IF(SUM(A2:A625)>3,1,""))</TD></TR><TR><TD>A2</TD><TD>=IF(LEFT(B2,7)="Merchan",4,IF(SUM(A3:A626)>3,1,""))</TD></TR><TR><TD>A3</TD><TD>=IF(LEFT(B3,7)="Merchan",4,IF(SUM(A4:A627)>3,1,""))</TD></TR><TR><TD>A4</TD><TD>=IF(LEFT(B4,7)="Merchan",4,IF(SUM(A5:A628)>3,1,""))</TD></TR><TR><TD>A5</TD><TD>=IF(LEFT(B5,7)="Merchan",4,IF(SUM(A6:A629)>3,1,""))</TD></TR><TR><TD>A6</TD><TD>=IF(LEFT(B6,7)="Merchan",4,IF(SUM(A7:A630)>3,1,""))</TD></TR><TR><TD>A7</TD><TD>=IF(LEFT(B7,7)="Merchan",4,IF(SUM(A8:A631)>3,1,""))</TD></TR><TR><TD>A8</TD><TD>=IF(LEFT(B8,7)="Merchan",4,IF(SUM(A9:A632)>3,1,""))</TD></TR><TR><TD>A9</TD><TD>=IF(LEFT(B9,7)="Merchan",4,IF(SUM(A10:A633)>3,1,""))</TD></TR><TR><TD>A10</TD><TD>=IF(LEFT(B10,7)="Merchan",4,IF(SUM(A11:A634)>3,1,""))</TD></TR><TR><TD>A11</TD><TD>=IF(LEFT(B11,7)="Merchan",4,IF(SUM(A12:A635)>3,1,""))</TD></TR><TR><TD>A12</TD><TD>=IF(LEFT(B12,7)="Merchan",4,IF(SUM(A13:A636)>3,1,""))</TD></TR><TR><TD>A13</TD><TD>=IF(LEFT(B13,7)="Merchan",4,IF(SUM(A14:A637)>3,1,""))</TD></TR><TR><TD>A14</TD><TD>=IF(LEFT(B14,7)="Merchan",4,IF(SUM(A15:A638)>3,1,""))</TD></TR><TR><TD>A15</TD><TD>=IF(LEFT(B15,7)="Merchan",4,IF(SUM(A16:A639)>3,1,""))</TD></TR><TR><TD>A16</TD><TD>=IF(LEFT(B16,7)="Merchan",4,IF(SUM(A17:A640)>3,1,""))</TD></TR><TR><TD>A17</TD><TD>=IF(LEFT(B17,7)="Merchan",4,IF(SUM(A18:A641)>3,1,""))</TD></TR><TR><TD>A18</TD><TD>=IF(LEFT(B18,7)="Merchan",4,IF(SUM(A19:A642)>3,1,""))</TD></TR><TR><TD>A19</TD><TD>=IF(LEFT(B19,7)="Merchan",4,IF(SUM(A20:A643)>3,1,""))</TD></TR><TR><TD>A20</TD><TD>=IF(LEFT(B20,7)="Merchan",4,IF(SUM(A21:A644)>3,1,""))</TD></TR><TR><TD>A21</TD><TD>=IF(LEFT(B21,7)="Merchan",4,IF(SUM(A22:A645)>3,1,""))</TD></TR><TR><TD>A22</TD><TD>=IF(LEFT(B22,7)="Merchan",4,IF(SUM(A23:A646)>3,1,""))</TD></TR><TR><TD>A23</TD><TD>=IF(LEFT(B23,7)="Merchan",4,IF(SUM(A24:A647)>3,1,""))</TD></TR><TR><TD>A24</TD><TD>=IF(LEFT(B24,7)="Merchan",4,IF(SUM(A25:A648)>3,1,""))</TD></TR><TR><TD>A25</TD><TD>=IF(LEFT(B25,7)="Merchan",4,IF(SUM(A26:A649)>3,1,""))</TD></TR><TR><TD>A26</TD><TD>=IF(LEFT(B26,7)="Merchan",4,IF(SUM(A27:A650)>3,1,""))</TD></TR><TR><TD>A27</TD><TD>=IF(LEFT(B27,7)="Merchan",4,IF(SUM(A28:A651)>3,1,""))</TD></TR><TR><TD>A28</TD><TD>=IF(LEFT(B28,7)="Merchan",4,IF(SUM(A29:A652)>3,1,""))</TD></TR><TR><TD>A29</TD><TD>=IF(LEFT(B29,7)="Merchan",4,IF(SUM(A30:A653)>3,1,""))</TD></TR><TR><TD>A30</TD><TD>=IF(LEFT(B30,7)="Merchan",4,IF(SUM(A31:A654)>3,1,""))</TD></TR><TR><TD>A31</TD><TD>=IF(LEFT(B31,7)="Merchan",4,IF(SUM(A32:A655)>3,1,""))</TD></TR><TR><TD>A32</TD><TD>=IF(LEFT(B32,7)="Merchan",4,IF(SUM(A33:A656)>3,1,""))</TD></TR><TR><TD>A33</TD><TD>=IF(LEFT(B33,7)="Merchan",4,IF(SUM(A34:A657)>3,1,""))</TD></TR><TR><TD>A34</TD><TD>=IF(LEFT(B34,7)="Merchan",4,IF(SUM(A35:A658)>3,1,""))</TD></TR><TR><TD>A35</TD><TD>=IF(LEFT(B35,7)="Merchan",4,IF(SUM(A36:A659)>3,1,""))</TD></TR><TR><TD>A36</TD><TD>=IF(LEFT(B36,7)="Merchan",4,IF(SUM(A37:A660)>3,1,""))</TD></TR><TR><TD>A37</TD><TD>=IF(LEFT(B37,7)="Merchan",4,IF(SUM(A38:A661)>3,1,""))</TD></TR><TR><TD>A38</TD><TD>=IF(LEFT(B38,7)="Merchan",4,IF(SUM(A39:A662)>3,1,""))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[VoG]

If you have a unique identifier in a column then look here http://www.mrexcel.com/forum/showthread.php?t=396069

 

[blkmagik]

Hey there VoG thank for replying back to me.

With regards to your suggestion, im assuming that you recommend Datapig? Yes its a neat addin however, i dont think it suites my issue entierly.

I need to retain the data below each instance of the word Merchandise and keep the same data integrity.

What i need is to find a way that will allow me to over come the insert pagebreak limitiation by splitting the file into separate workbooks, by spliting the data where the limitations would normaly happen (e.g. every 1000th pagebreak)

Is there another way? thanks

 

[parry]

Hi, can I ask why so many pages are required? I would have thought Transposing the data into rows would be more efficient and enable multiple items to be evaluated.

 

[blkmagik]

Hi parry,

Well the report are downloaded from an external website, this its reprinted and scanned into an OCR program. It is necessary to keep the data intact exactly as it is viewed.

The sample i gave does not include several columns with the actuall data on it. Its more or less an example of the data structure.

So is there a possbile way for me to over ride the page break limitation or a macro that would enable me to split the document every 1000th instance of where an automated page break macro would fail?

 

[parry]

Hi, you haven't posted the code that inserts the page breaks but assuming you can adjust this accordingly to your needs you can try something like....

 If ActiveSheet.HPageBreaks.Count >= 1000 Then
        MsgBox "The 1,000th page starts at row " & ActiveSheet.HPageBreaks.Item(ActiveSheet.HPageBreaks.Count).Location
        Exit Sub
    End If

 

[blkmagik]

Nope it doesnt work, still giving me the pagebreak limit message.
Here is the code i use.

Sub RaPageBreaks()

Dim c As Range
Dim firstaddress As String
Dim Search As String
Dim Prompt As String
Dim Title As String
Application.ScreenUpdating = False
Prompt = "What do you want to search for?"
Title = ""
Search = "Merchandise was returned to:"
' Search for cell with "Merchandise was returned to:"
If Search = "" Then
Exit Sub
' IF Search fails exit routine
End If
With ActiveSheet.UsedRange
Set c = .Find(What:=Search, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
If Not c Is Nothing Then
firstaddress = c.Address
Do
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=c
Set c = .FindNext(c)
' IF Search finds "Merchandise was returned to:" insert pagebreak in the row above
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With
End Sub

 

[parry]

Not surprised it doesn't work - cant see it in your code and I did say adjust to your needs. Anyways, as you are adding a page break after every item then why not simply add a counter and when you reach 999 then stop?

 

[blkmagik]

Hey parry,
Unfortunately im not that profficient enough i the language to know how to add a counter. The other issue is,even if i were even able to figure out how to add a counter, i still would not be able to add page breaks to the remainder of the file since it has reached its limit of allowable pagebreaks. So the only sound solution is somehow breaking the file into separate workbooks or worksheets, right?