Burst Worksheets from Pivot Table

[srdavisgb]

I want to create worksheets in a workbook based on criteria used in a Pivot Table... I think I've seen a podcast that demonstrates how this can be done but, I can't find it.

For instance, if the pivot table rows contain the States and the value columns contain a count of cities, when you click on the city count a new worksheet opens with all the data for that State (e.g., row). I want to create worksheets for all the States in the same workbook. I'd also be interested in how you would do the same thing but, create the individual State worksheets in new Workbooks.

How do you automate the process and create worksheets re-named for each State that contain a list of all the cities for that State?

I am somewhat familiar with VBA.

Thanks in advance for your assistance.

 

[Trevor G]

Once you create the Pivot Table you can set to show Pages and then select the show pages to give you an individual Pivot per sheet and it names the sheets for you. Depending on which version depends on how to find the show pages.

If using 2003 or less then click in the Pivot table and in the Pivot table toolbar select the Pivot Table button and then Show Pages

If using 2007 or higher then click in the Pivot table and then in the Options Tab at the very left click the Options down arrow and then Show Report Filter Pages

So all you have to do then is design the Pivot in the way you want it. If you then redesign after you run the first Report Pages then go back and redesign you run it again and it will give you a different report.

 

[srdavisgb]

Thanks Trevor,

This feature works as described - a new sheet is created with the respective report filter row information for the pivot table. However, I would like to burst the pivot table to display the data in the individual worksheets. For instance, the source of the pivot table data has 20 columns and there are 10 values displayed in the Row Labels of the pivot table. When the pivot table is burst, there are 10 worksheets named for each row label with 20 columns of data in each worksheet.

Is the a feature or a VBA routine to do this?

Again, your assistance is appreciated. Thank you.

 

[Trevor G]

I am not to sure if this is possible. But take a look a this website to see if it helps in anyway. There are also lots of questions below the sample stages

Referencing Pivot Table Ranges in VBA | Peltier Tech Blog | Excel Charts