Results 1 to 2 of 2

Thread: Breaking excel tab into multiple worksheets and maintaining 2 header rows

  1. #1
    Junior Member
    Join Date
    May 2016
    Posts
    1
    Rep Power
    0

    Breaking excel tab into multiple worksheets and maintaining 2 header rows

    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.

    Capture.GIF

    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

  2. #2
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    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-q...ed-column.html
    http://www.mrexcel.com/forum/excel-q...-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-...ml#post4109080
    or
    code Alternatives:
    http://www.excelfox.com/forum/showth...7-BBCode-Table
    http://www.excelfox.com/forum/showth...=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/forumd...p/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 )
    Last edited by DocAElstein; 05-28-2016 at 01:50 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

Similar Threads

  1. Excel Macro to Split Multiple Columns into rows
    By cali-novice in forum Excel Help
    Replies: 2
    Last Post: 05-16-2014, 12:34 PM
  2. Delete Filtered Rows Excluding The Header
    By xander1981 in forum Excel Help
    Replies: 5
    Last Post: 04-01-2014, 11:44 PM
  3. Maintaining Vouchers in Excel
    By msiyab in forum Excel Help
    Replies: 1
    Last Post: 02-25-2013, 01:48 PM
  4. Replies: 9
    Last Post: 12-04-2012, 09:45 PM
  5. Meger multiple file but header not same
    By rocky in forum Excel Help
    Replies: 14
    Last Post: 10-25-2012, 09:09 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •