Split one table into multiple sheets based on criteria

Kyosti

Board Regular
Joined
Jun 2, 2008
Messages
83
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
Joined
Jun 29, 2014
Messages
837
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
Joined
Jun 12, 2014
Messages
30,575
Office Version
365
Platform
Windows
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
Joined
Jun 2, 2008
Messages
83
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
Joined
Jun 2, 2008
Messages
83
YOU ARE MY HERO!!! This works perfectly!!!! THANK YOU!! (y)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,575
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Kyosti

Board Regular
Joined
Jun 2, 2008
Messages
83
@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!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,575
Office Version
365
Platform
Windows
If it's not regular data, what is it?
 

Kyosti

Board Regular
Joined
Jun 2, 2008
Messages
83
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
Joined
Jun 12, 2014
Messages
30,575
Office Version
365
Platform
Windows
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
 

Forum statistics

Threads
1,078,544
Messages
5,341,086
Members
399,417
Latest member
Andrea Elyas

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top