Split one table into multiple sheets based on criteria

K

Kyosti

Board Regular
Joined
Jun 2, 2008
Messages
90
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.


[TABLE="width: 1604"]
<colgroup><col span="14"><col></colgroup><tbody>[TR]
[TD]Field 1[/TD]
[TD]Field 2[/TD]
[TD]Field 3[/TD]
[TD]Field 4[/TD]
[TD]Field 5[/TD]
[TD]Field 6[/TD]
[TD]Field 7[/TD]
[TD]Field 8[/TD]
[TD]Field 9[/TD]
[TD]Field 10[/TD]
[TD]Field 11[/TD]
[TD]Field 12[/TD]
[TD]Field 13[/TD]
[TD]Field 14[/TD]
[TD]Vendor Name[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Access General[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Access General[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Access General[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Access General[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Ace Insurance Group[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Ace Insurance Group[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Ace Insurance Group[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Ace Insurance Group[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Allianz[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Allianz[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AMIG[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AMIG[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AMIG[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AMIG[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AMIG[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AMIG[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AMIG[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AMIG[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
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.
 
Upvote 0
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
 
Upvote 0
Thank you for looking at my post. I thought I did upload it, but it seems that Fluff has solved my problem. Thanks
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
@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!
 
Upvote 0
If it's not regular data, what is it?
 
Upvote 0
The first sheet is the only one that creates a secondary table and filters the data out instead of separating it out.
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
VBA/Macro: Adding Pivot table with connection string
Replies
1
Views
156
Jette3173
J
E
  • Question Question
Write formula to compare planned amounts by vendor in one sheet to approved amounts by vendor in another tab based on project ID
Replies
1
Views
548
OaklandJim
O
B
  • Question Question
Update Variable Data from Multiple Worksheets in a Master Sheet
2
Replies
11
Views
757
mumps
mumps
W
  • Question Question
Macro/VBA code code for creating new excel file for each pivot table from a column filter
Replies
1
Views
442
Anthony47
A
T
  • Solved
VBA Macro to sort table by vendor copy table into email body and send, then do the same for the next vendor in the table to the last vendor
Replies
5
Views
1K
takingiwhanau
T

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,623
Latest member
Techenthusiast

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top