Create Worksheet for Each Pivot Item

Last week, Angella emailed me, to ask if there was a way to create a copy of a pivot table, for each item in a report filter.

I suggested that she use a built-in pivot table feature, which is designed to do that – Show Report Filter Pages. The instructions for using this feature are shown below.

However, this built-in feature only copies the pivot table to a new sheet – it doesn’t copy any other content from the original sheet. Angella wanted the pivot table, and she wanted all the other content too.

So, I created a macro that will copy the original sheet, name the copied sheet, and select the pivot item in the copied sheet’s pivot table. You can see the sample code on my Contextures site.

If you just need a copy of the pivot table, you can do the job quickly and easily with the Show Report Filter Pages feature.

Copy Pivot Table with Show Report Filter Pages

To create a quick copy of a pivot table, for each item in a Report Filter field, follow these steps:

1. Select a cell in the pivot table (the pivot table must have at lease one Report Filter)
2. On the Ribbon, under the PivotTable Tools tab, click the Options tab
3. At the left, click Options, then click Show Report Filter Pages
4. In the Show Report Filter Pages dialog box, select one of the filters, and click OK
5. A new worksheet will be added for each* pivot item, named for the pivot item. (*see exceptions below)

Note: If a sheet with the item’s name already exists, another sheet will be added, with a number added at the end.

Report Filter Page Exceptions

When you run the Show Report Filter Pages command, it might not create a new sheet for each item in the Report Filter. The results will be affected by

• the setting for Select Multiple Items
• which items are currently selected in filter

Select Multiple Items Turned Off

• If (All) is selected, a sheet should be created for each item.
• If a specific item is selected, a sheet will not be created for that item. I guess that Excel assumes that since you already have a pivot table with that item’s data, you don’t need another one.

Select Multiple Items Turned On

• If (All) is selected, a sheet should be created for each item.
• If one item is selected, a sheet will be created for that item only.
• If two or more items are selected (Multiple Items), a sheet will be created for each selected item.

Note: Select Multiple Items is turned off in the pivot table copies, even if it is turned on in the original pivot table.

Use VBA to Copy the Entire Worksheet

If you want to copy all the other content from the original worksheet, along with the pivot table, you can sample code on my Contextures site on my Contextures site. My code adds a “PT_” prefix to the sheets, and deletes any existing “PT_” sheets, if necessary.

___________________