Breaking excel tab into multiple worksheets and maintaining 2 header rows

[mthomas]

Hello there.. brand new to this and could really use some help. I have a tab with two header rows, one with notes/column descriptions and the second with actual column headers.

1827

I am running this code to break out the tab based on column A, cell 2 which is the manager name.

Sub parse_data()
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
vcol = 1
Set ws = Sheets("BC")
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = "A1:AJ2"
titlerow = ws.Range(title).Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
For i = 2 To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns (icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Na me = myarr(i) & ""
Else
Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
End If
ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1:AI2")
Sheets(myarr(i) & "").Columns.AutoFit
Next
ws.AutoFilterMode = False
ws.Activate
End Sub


I am only getting the first row with the descriptors and need to know how to get 2 rows included.

Also, how can I get this to save as new individual workbooks instead of new tabs on the same workbook?

Thanks in advance,
Mark

[DocAElstein]

Mark,
Welcome to the board :)
I recognise that code and have done many variations of it, for example here
http://www.mrexcel.com/forum/excel-questions/727407-visual-basic-applications-split-data-into-multiple-worksheets-based-column.html
http://www.mrexcel.com/forum/excel-questions/727407-visual-basic-applications-split-data-into-multiple-worksheets-based-column-3.html

You need to give us some more specific detail and some test data to work on.
Remember to reduce the amount of test data to the minimum required to demonstrate typical scenarios. Desensitize the data if necessary.

Show us clearly what you have and the results you want to achieve.
Please do not use images as we cannot copy them to a worksheet.
Instead:
Either
use an Add-In or similar tools to produce screenshots that we can copy.
-See my signature below,
http://www.excelforum.com/the-water-cooler/1068075-just-testing-img-cannot-do-it-in-test-forum-as-img-is-off-there-no-reply-needed-2.html#post4109080
or
code Alternatives:
http://www.excelfox.com/forum/showthread.php/2077-BBCode-Table
http://www.excelfox.com/forum/showthread.php/2079-test-BB-Code?p=9804#post9804
Collection of codes here
https://app.box.com/s/zhz7awdag4nl1zs6564s9zzcwp50e4w9
After selecting a Spreadsheet range run the ' MAIN CODE in any of the modules

Basically to use those tools you highlight the part of your Spreadsheet of interest, and then run the code which copies that screenshot in a BB Code Tag type format into your Clipboard. You then paste that into the Post Editor window. When posted it comes out in a formatted table. Practice using those tools in our Test Sub Forum. To do that start a new “test” thread here and practice!!!
http://www.excelfox.com/forum/forumdisplay.php/17-Test-Area
_....
or
Upload a File
Select the Paperclip icon above. To get the Paperclip icon in a Reply window you will need to select the Go Advanced Button


Alan

P.s. Please use Code Tags ;) ( see my signature below )