Copying Rows between specific Start and Stop values?

[PatNOregon]

Hey Guys, first time poster, what a resource this is!
I have a project where I need to pull data from a reference file that has this kind of format:

Report for: Area A
Data
Data
Data
Report for: Area B
and so on.

Is there a way I can have specific worksheets pull only the title row that matches and then all the rows after it, UNTIL it reaches another "Report For" line. Meaning that the next subreport is about to start but I dont want the data mixed up.

So Worksheet A sould have:
Report for: Area A
Data
Data
Data

and be done.
Then Area B would be on another sheet.

Anyone got some tricks?
Thanks!

 

[Tazguy37]

Welcome to MrExcel Board! I came up with some VBA code that should do the trick:

Sub testSeparate()
Dim lastRow As Long, i As Long, j As Long

i = 1
j = 1
lastRow = Worksheets("Reports").Range("a65536").End(xlUp).Row

Do While i < lastRow
    If Left(Cells(i, 1).value, 11) = "Report for:" Then
        Do
            j = j + 1
        Loop Until (Left(Cells(j, 1).value, 11) = "Report for:" Or j > lastRow)
        Rows(i & ":" & j - 1).Copy
        Worksheets.Add
        Range("A1").PasteSpecial xlPasteValues
        Application.CutCopyMode = False
        Worksheets("Reports").Activate
        i = j
    Else
        i = i + 1
    End If
Loop

End Sub

Press Alt-F11 to open the VBE.
Press Control-R to open the Project Explorer.
Click "Microsoft Excel Objects" for the file you're working on.
Go to the Insert menu, and select Module.
Open the Code pane with F7.
Paste the above code in.
Press Alt-Q to close the VBE and return to Excel.

To run this macro, go to Tools, Macro, Macros (or Press Alt-F8),
click the name of the routine you just created, and select Run.

If you have any problems with entering or executing the code, post back, and someone will be able to help you.
EDIT: Forgot to mention that this code looks for a sheet called "Reports" (where all your original data is). Just change that name in the code to your sheet name.

 

[PatNOregon]

Ok, this is a good start but still not "exactly" what I need. I tried tweaking the code but to no avail.
So I have a master file with one sheet and over 50K rows which are broken up by Area using the text already referred to.
I have 11 other workbooks with numerous sheets all referring to areas in the master file.
Though I can get the master file to break down into numerous sheets of each area (is there any way to use the string of the IF statement as the name of the new sheet?)
I still can get the seperate worksheets to pull in the data from the master file. I've adjusted the Left function to up the string comparison to about 40 characters for some areas but it wont even find a match.

So say my master file has 10 areas in it ( 1-10), and two other worksheets contain odd areas in one, even areas in the other. How do I get those areas copied from my master file automatically based on a string compare?
Thanks for the help!