If you mean to say all headers are in 7 lines; Adjust the below 3 lines in
the code

for 7 header lines
varTemp = Rows(lngRow - 6 & ":" & lngLastRow)
Rows(lngRow - 6 & ":" & lngLastRow).ClearContents
Rows(1 & ":" & lngLastRow - lngRow + 8) = varTemp

I have modified the earlier code a bit...

Sub Macro()
Dim lngRow As Long, lngLastRow As Long, varTemp As Variant
Dim intCount As Integer
lngRow = 1
lngLastRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, SearchOrder:=xlRows).Row
Do While lngRow <= lngLastRow
If Range("A" & lngRow) Like "[A-Z][A-Z]##" Then
intCount = intCount + 1
If intCount = 2 Then
varTemp = Rows(lngRow - 1 & ":" & lngLastRow)
Rows(lngRow - 1 & ":" & lngLastRow).ClearContents
Sheets.Add After:=ActiveSheet
Rows(1 & ":" & lngLastRow - lngRow + 2) = varTemp
lngRow = 1
intCount = 0
lngLastRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, SearchOrder:=xlRows).Row
End If
End If
lngRow = lngRow + 1
Loop
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Marica" wrote:

Thanks Jacob,

It almost worked. The report I tested it on consisted of 3 cost centres. It
split the first 2 perfectly and it pulled the header of the 3rd into a new
sheet, but it left the transactional data on the original sheet.

Also, I should probably have mentioned that the header line consists of the
first 7 lines in the report.


"Jacob Skaria" wrote:

Take a backup of your actual data and test the below macro with your data in
Sheet1.The macro works on the active sheet..I assume your first cost centre
is in Row2 (just below the header row).

Sub Macro()
Dim lngRow As Long, lngLastRow As Long, varTemp As Variant
lngRow = 3
lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
Do While lngRow <= lngLastRow
If Range("A" & lngRow) Like "[A-Z][A-Z]##" Then
varTemp = Rows(lngRow - 1 & ":" & lngLastRow)
Rows(lngRow - 1 & ":" & lngLastRow).ClearContents
Sheets.Add After:=ActiveSheet
Rows(1 & ":" & lngLastRow - lngRow + 2) = varTemp
lngRow = 3
lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
End If
lngRow = lngRow + 1
Loop
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Marica" wrote:

Jacob,

To answer your queries:
1. When the report pulls from Dynamics it adds a header in excel for every
page that existed in the Dynamics report. This results in multiple headings
that inbedded inbetween the data. So in instances where the report spans 5
pages there will be 5 headings eventhough I only have 2 cost centres.
2. The report spans to Column M.
3. The report pulls the formatting from Dynamics so I don't require any
formatting. Except if it is possible to get rid of all the additional headers
without the "manual delete" :-)

Thanks for your response.


"Jacob Skaria" wrote:

Marica

Few queries
1. If you have headers repeated for each cost centre, why dont you split by
the header rather than 2alpha/2numeric entry in Column A.. OR do you forsee
any issues
2. Report spans to which column..(not really needed but still)
3. Do you need the formatting Or just the data in the new sheets..

If this post helps click Yes
---------------
Jacob Skaria


"Marica" wrote:

I need to split a worksheet into multiple worksheets. The report pulled from
Dynamics creates one excel worksheet for multiple cost centres. I need to
split the report so that every cost centre is on a different worksheet. The
reports have different lenghts so I cannot make use of a "every 100th line"
approach. The common denominator is that when a new report starts column A
has a value with 2 alphabetical characters and 2 numerical characters. Can I
somehow make use of this to indicate to excel to split the report when it
encounters a value with 2 alphabetical characters and 2 numerical characters
in Column A?

Example:

REPORT HEADING
HO13 data
data
data etc
REPORT HEADING
LB17 data
data etc

The result I require is that HO13 will remain on the current worksheet and
LB17 will move to a new worksheet (including his report heading). Please keep
in mind that I might have more than 2 cost centres on the report (this will
also vary with every report generated).

If anybody could be of assistance I would greatly appreciate. Please provide
the explanation for dummies.

Thanks