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

Thread: Split one table into multiple sheets based on criteria

  1. #1
    Board Regular
    Join Date
    Jun 2008
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Split one table into multiple sheets based on criteria

    PLEASE HELP!!! This community has never let me down and I doubt you guys will start now!!

    I have a table on a Data tab. This data has multiple values for the Vendor Name column. I need to create separate sheets based on each individual vendor name and name each sheet by each vendor. I don't know how this can be done via a macro, but I have tried and I get stuck several times. I have attached the file so you can see how the data will be laid out. Please let me know if you guys can help!!

    Idealistically if there is no data for a specific vendor they would be skipped, but that is not so important. If I can get a macro to split each vendor out that is really what I need.

    Thanks in advance.


    Field 1 Field 2 Field 3 Field 4 Field 5 Field 6 Field 7 Field 8 Field 9 Field 10 Field 11 Field 12 Field 13 Field 14 Vendor Name
    Access General
    Access General
    Access General
    Access General
    Ace Insurance Group
    Ace Insurance Group
    Ace Insurance Group
    Ace Insurance Group
    Allianz
    Allianz
    AMIG
    AMIG
    AMIG
    AMIG
    AMIG
    AMIG
    AMIG
    AMIG
    Last edited by Kyosti; Jun 18th, 2019 at 04:37 PM. Reason: updated question

  2. #2
    Board Regular
    Join Date
    Jun 2014
    Posts
    837
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Split one table into multiple sheets based on criteria

    Hello Kyosti,

    We don't see a sample file attached.

    Please upload a sample of your workbook to a file sharing site such as ge.tt or Drop Box and then post the link to your file back here. If your data is sensitive then please use dummy data. Please ensure that your sample is an exact replica of your actual workbook.

    Cheerio,
    vcoolio.

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,341
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Split one table into multiple sheets based on criteria

    How about
    Code:
    Sub Kyosti()
       Dim Cl As Range
       Dim Ws As Worksheet
       Dim Ky As Variant
       
       Set Ws = Sheets("Data")
       With CreateObject("scripting.dictionary")
          For Each Cl In Ws.Range("O2", Ws.Range("O" & Rows.Count).End(xlUp))
             .Item(Cl.Value) = Empty
          Next Cl
          For Each Ky In .Keys
             Ws.Range("A1:O1").AutoFilter 15, Ky
             Sheets.Add(, Sheets(Sheets.Count)).Name = Ky
             Ws.AutoFilter.Range.EntireRow.Copy Range("A1")
          Next Ky
          Ws.AutoFilterMode = False
       End With
    End Sub
    Change sheet name to suit
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    Board Regular
    Join Date
    Jun 2008
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Split one table into multiple sheets based on criteria

    Thank you for looking at my post. I thought I did upload it, but it seems that Fluff has solved my problem. Thanks

  5. #5
    Board Regular
    Join Date
    Jun 2008
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Split one table into multiple sheets based on criteria

    YOU ARE MY HERO!!! This works perfectly!!!! THANK YOU!!

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,341
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Split one table into multiple sheets based on criteria

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular
    Join Date
    Jun 2008
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Split one table into multiple sheets based on criteria

    @Fluff

    One additional question. All of the tabs created are regular data ranges, except for the very first one it creates... what would be causing that? The other sheets being regular data ranges works well with what I am attempting to do.

    Thanks again!

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,341
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Split one table into multiple sheets based on criteria

    If it's not regular data, what is it?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    Board Regular
    Join Date
    Jun 2008
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Split one table into multiple sheets based on criteria

    The first sheet is the only one that creates a secondary table and filters the data out instead of separating it out.

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,341
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Split one table into multiple sheets based on criteria

    Ok, try
    Code:
    Sub Kyosti()
       Dim Cl As Range
       Dim Ws As Worksheet
       Dim Ky As Variant
       
       Set Ws = Sheets("Data")
       With CreateObject("scripting.dictionary")
          For Each Cl In Ws.Range("O2", Ws.Range("O" & Rows.Count).End(xlUp))
             .Item(Cl.Value) = Empty
          Next Cl
          For Each Ky In .Keys
             Ws.Range("A1:O1").AutoFilter 15, Ky
             Sheets.Add(, Sheets(Sheets.Count)).Name = Ky
             Ws.AutoFilter.Range.SpecialCells(xlVisible).EntireRow.Copy Range("A1")
          Next Ky
          Ws.AutoFilterMode = False
       End With
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

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
  •