Hi,

I have 3 pivot tables in a file. Each can be filtered by state. I am interested in creating one file per state such that it has 3 tabs and the filtered values for a state from each pivots in each tab.
In the original master excel - one tab has summary for ALL the states, 2nd has details for ALL the states, 3rd has additional data for ALL the states. ' State' is the filter for each pivot.
I am looking to get ONE file per state such that for e.g. - for Illinois, my new file will have 3 tabs - IL-Summary, IL-Details, IL-Additional data.

I know I can separate the data for each state using 'Show report filter pages' under pivot table options, but that only creates another tab IN the same excel workbook, so when I apply it to the first Pivot, it creates 50+ tabs for each state labelled AK, AL, AZ, IL etc. Then when I apply it to the other pivot, it creates, AK(1), AL(1), AZ(1) etc. and the for the 3rd AK(2), AL(2) ...etc.
I then have to combine AK, AK(1) and AK(2) and then repeat this for 50+ states.

Also, I don't want data for one state to be visible to others and so I need ONLY the values from the pivot. When I use the 'Show report filter pages' options, it just creates another pivot on a new tab and the filter can be removed to see the data for each state.

Is there a way to do this other than manual?