Split one table into multiple sheets based on criteria

Kyosti

Board Regular
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 1Field 2Field 3Field 4Field 5Field 6Field 7Field 8Field 9Field 10Field 11Field 12Field 13Field 14Vendor 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

<colgroup><col span="14"><col></colgroup><tbody>
</tbody>
 
Last edited:

vcoolio

Well-known Member
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.
 

Fluff

MrExcel MVP, Moderator
How about
Code:
Sub Kyosti()
   Dim Cl As Range
   Dim Ws As Worksheet
   Dim Ky As Variant
   
   Set Ws = Sheets("[COLOR=#ff0000]Data[/COLOR]")
   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
 

Kyosti

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

Kyosti

Board Regular
YOU ARE MY HERO!!! This works perfectly!!!! THANK YOU!! (y)
 

Kyosti

Board Regular
@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!
 

Kyosti

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

Fluff

MrExcel MVP, Moderator
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
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top