VBA: Split a table into multiple tables across different worksheets?

[George201]

Hi, I'm fairly new to VBA and am trying to create some code to search through two columns in a table for specific criteria, create a new worksheet and extract these rows from the old table to a new one on the new worksheet.


For example using the table below. I'd like to look for rows containing Government, and then create a new worksheet for each country copying the rows over for that country. In the table I am using, countries are mentioned by two letter country code and full name.

Segment	Country	Product	Discount Band	Units Sold	Manufacturing Price	Sale Price	Gross Sales
Government	Canada	Carretera	None	1618.5​	$ 3.00	$ 20.00	$ 32,370.00
Government	Germany	Carretera	None	1321​	$ 3.00	$ 20.00	$ 26,420.00
Midmarket	France	Carretera	None	2178​	$ 3.00	$ 15.00	$ 32,670.00
Midmarket	Germany	Carretera	None	888​	$ 3.00	$ 15.00	$ 13,320.00
Midmarket	Mexico	Carretera	None	2470​	$ 3.00	$ 15.00	$ 37,050.00
Government	Germany	Carretera	None	1513​	$ 3.00	$ 350.00	$ 529,550.00
Midmarket	Germany	Montana	None	921​	$ 5.00	$ 15.00	$ 13,815.00
Channel Partners	Canada	Montana	None	2518​	$ 5.00	$ 12.00	$ 30,216.00
Government	France	Montana	None	1899​	$ 5.00	$ 20.00	$ 37,980.00
Channel Partners	Germany	Montana	None	1545​	$ 5.00	$ 12.00	$ 18,540.00
Midmarket	Mexico	Montana	None	2470​	$ 5.00	$ 15.00	$ 37,050.00
Enterprise	Canada	Montana	None	2665.5​	$ 5.00	$ 125.00	$ 333,187.50
Small Business	Mexico	Montana	None	958​	$ 5.00	$ 300.00	$ 287,400.00
Government	Germany	Montana	None	2146​	$ 5.00	$ 7.00	$ 15,022.00

Again I am very new to VBA so any guidance or help is appreciated! Thanks

 

[dermie_72]

have you tried recording a macro of you adding a filter, setting the filter to show just goverment then copying all visible cells into a new sheet?
You could then review the code you created and adjust the to suit your needs.