Generating individual tabs of each state from master workbook

Hi - At work, one of our managers asked if there is an easy way to automatically create subsets in tabs of a huge master spreadsheet (with exact same rows/columns in each tab).

More specifically, this spreadsheet consists of all states and the goal is to create spreadsheets for each individual state, so that it has it's own self contained TAB and view. Currently filtering by state code is used, but separate breakouts would be helpful into some special automation.

One idea I had was to manually setup an individual state tab, and then place copy reference in the cell with an IF statement to select just the rows containing that state (we'd most likely have 25 and not all 50 states). Pivoting also came to mind although I've not gone beyond the basics.

Most likely our IT developers (who are more versed in VBA) might help on this, but just to wanted to ask for ideas by some of experts here.

Thanks for any ideas on the best approach

I've attached a very simple XLS attachment as an example of the desired output



MASTER SPREAD SHEET IS KEYED BY STATE ABBREVIATION
AL .... data in columns
VA .... data in columns
NY .... data in columns
VA .... data in columns
WV .... data in columns
AL .... data in columns
VA .... data in columns
NY .... data in columns
VA .... data in columns
WV .... data in columns


END RESULTS IS TO COPY ALL DATA FROM MASTER SPREADSHEET FOR EACH STATE INTO IT'S OWN INDIVIDUAL VIEWING TABS

<---- TABS ------>
| xx | xx | xx | xx | .... data only for specific state in each tab
| AL |NY |WV| VA|

Hi harrywaldron
I first thing will be to work out the List of names for the worksheets
Do you want the/a answer or just ideas?

Try this on a test book:

Please Login or Register  to view this content.

Thanks for both replies ... Leah thanks also for the VBA code as it seems to work well