Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: create sep sheets based on data in column
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2011
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question create sep sheets based on data in column

    Hello,

    I am trying to figure out how i can create sep sheets in a workbook based on data on sheet 1 column A... along with the header info in row 1 of sheet 1 and all the respective rows for that sheet.. Any help would be great... ie data sample

    dept emply # dept name blah blah
    1221 1023 xxx xxx xxx
    1221 1023 xxx xxx xxx
    991 451 xx xxx xxx
    10451 6533 xxx xxx xxx

    so then this would create 3 differ sheets based on Dept. column and then also copy the header row... ea. sheet would be named on Column A data also.

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: create sep sheets based on data in column

    Try:

    Code:
    Sub Test()
        Dim Sh As Worksheet
        Dim Rng As Range
        Dim c As Range
        Dim List As New Collection
        Dim Item As Variant
        Dim ShNew As Worksheet
        Application.ScreenUpdating = False
    '   *** Change Sheet name to suit ***
        Set Sh = Worksheets("Sheet1")
        Set Rng = Sh.Range("A2:A" & Sh.Range("A65536").End(xlUp).Row)
        On Error Resume Next
        For Each c In Rng
            List.Add c.Value, CStr(c.Value)
        Next c
        On Error GoTo 0
        Set Rng = Sh.Range("A1:E" & Sh.Range("A65536").End(xlUp).Row)
        For Each Item In List
            Set ShNew = Worksheets.Add
            ShNew.Name = Item
            Rng.AutoFilter Field:=1, Criteria1:=Item
            Rng.SpecialCells(xlCellTypeVisible).Copy ShNew.Range("A1")
            Rng.AutoFilter
        Next Item
        Sh.Activate
        Application.ScreenUpdating = True
    End Sub
    Microsoft MVP - Excel

  3. #3
    New Member
    Join Date
    Jul 2011
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: create sep sheets based on data in column

    Holly cr*p.. that worked like a charm....
    thx.. saved me alot of cutting and pasting... i sure wish i knew a lot more about codes and macros....

    again thanks.... heres to you...

  4. #4
    New Member
    Join Date
    Jul 2011
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: create sep sheets based on data in column

    can you tweek this a little. I only want th have the 1st row with all the heading info show up on all the sheets that get created....

    so based on my exp. above... only "dept, emply #, dept name, blah, blah"
    headers for Column a - e.

    thanks...

  5. #5
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: create sep sheets based on data in column

    Sorry, I don't know what you mean. The macro is copying the headings isn't it?
    Microsoft MVP - Excel

  6. #6
    New Member
    Join Date
    Jul 2011
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: create sep sheets based on data in column

    Quote Originally Posted by Andrew Poulsom View Post
    Sorry, I don't know what you mean. The macro is copying the headings isn't it?

    yes but it is also copying all the rows for that group. to ea. sheet...
    i just want the heading copied to ea. sheet i was wrong origianlly ... i thought i need the respective rows copied too... but i don't... or at least i don't think that will help me do what i need to do with the info....
    thx

  7. #7
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: create sep sheets based on data in column

    To copy just the header:

    Code:
    Sub Test()
        Dim Sh As Worksheet
        Dim Rng As Range
        Dim c As Range
        Dim List As New Collection
        Dim Item As Variant
        Dim ShNew As Worksheet
        Application.ScreenUpdating = False
    '   *** Change Sheet name to suit ***
        Set Sh = Worksheets("Sheet1")
        Set Rng = Sh.Range("A2:A" & Sh.Range("A65536").End(xlUp).Row)
        On Error Resume Next
        For Each c In Rng
            List.Add c.Value, CStr(c.Value)
        Next c
        On Error GoTo 0
        For Each Item In List
            Set ShNew = Worksheets.Add
            ShNew.Name = Item
            Rng.Cells(1, 1).EntireRow.Copy ShNew.Range("A1")
        Next Item
        Sh.Activate
        Application.ScreenUpdating = True
    End Sub
    Microsoft MVP - Excel

  8. #8
    New Member
    Join Date
    Jul 2011
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: create sep sheets based on data in column

    perfect>>> thank you very much... that worked beutifully.

  9. #9
    New Member
    Join Date
    Jan 2013
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: create sep sheets based on data in column

    Hi,
    I'm new to VBA.
    Could someone tell me what Im doing wrong here.
    Im trying to create and sort new sheets based on data in column D of my worksheet.
    Using code above i kinda have it working.
    It is creating a new sheet for each character listed in column D and is sorting them per sheet.
    The problem is that the range of data I need is from column A to P
    So far I can only get the code above only displays data from D to P.
    Any help would be great.
    Thanks to Mr. Excel VP for giving me something to start with
    Thanks.

    Heres the code:

    Sub Test()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim c As Range
    Dim List As New Collection
    Dim Item As Variant
    Dim ShNew As Worksheet
    Application.ScreenUpdating = False
    ' *** The job that this program needs to do ***
    ' *** Set Sheet 1, Column D as the first place to look, creating a sheet for each character listed in column D ***
    ' *** There are 8 characters listed in column D, so 8 sheets will be created ***
    ' *** These sheets will be populated with the entries found in sheet 1 that have corresponding character in column D ***

    Set Sh = Worksheets("Sheet1")
    Set Rng = Sh.Range("D1:D" & Sh.Range("D65536").End(xlUp).Row)
    On Error Resume Next
    For Each c In Rng
    List.Add c.Value, CStr(c.Value)
    Next c
    On Error GoTo 0

    ' *** This is where the problems start, for each entry in DB there is data stored from cells A to P
    ' *** Using the code below, Its works but I'm missing data from columns A to C
    ' *** If I set range to A1:P, it doesnt work and crashes, placing the same data in first row of each sheet
    ' *** How do I get data from columns A to C to display ?? (ie: All data from A to P) ***

    Set Rng = Sh.Range("D1:P" & Sh.Range("D65536").End(xlUp).Row)
    For Each Item In List
    Set ShNew = Worksheets.Add
    ShNew.Name = Item
    Rng.AutoFilter Field:=1, Criteria1:=Item
    Rng.SpecialCells(xlCellTypeVisible).Copy ShNew.Range("A1")
    Rng.AutoFilter
    Next Item
    Sh.Activate
    Application.ScreenUpdating = True
    End Sub

  10. #10
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: create sep sheets based on data in column

    Welcome to MrExcel.

    Try changing:

    Rng.SpecialCells(xlCellTypeVisible).Copy ShNew.Range("A1")

    to:

    Rng.EntireRow.SpecialCells(xlCellTypeVisible).Copy ShNew.Range("A1")
    Microsoft MVP - Excel

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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