Multiple Copies of Pivot Table Sheet

If you need to make a quick report, Excel has a built-in command that makes multiple copies of a pivot table – one for each item in a Report Filter. See how that feature works, its limitations, and a macro that will copy the entire sheet, not just the pivot table.

Create Pivot Table Copies

In this example, the pivot table has two fields in the Report Filter area – District and Rep. There are 4 names in the Rep field – Gil, Jones, Kivell and Parent.

Here’s how to create multiple copies of pivot table – one for each name in the Rep filter.

• Select a cell in the pivot table
• On the Ribbon, click the Analyze tab
• Click the Options arrow, then click Show Report Filter Pages

• If there are multiple Report Filters, a dialog box will appear
• Select the Rep field, and click OK

Multiple Copies of Pivot Table

After you click OK, Excel creates a copy of the pivot table for each name in the list of sales reps. Each pivot table is on its own worksheet, and has all the standard formatting from the original pivot table.

The Rep name is selected in the filter, and that name is on the sheet tab.

NOTE: Only the pivot table is copied – none of the other data or objects from the original sheet.

Important Note about Multiple Copies

If you use the Show Report Filter Pages feature, it’s important to note that all the data is included in each pivot table, not just the data for the Rep name that is shown in the filter.

Don’t send the pivot table copies to each person, assuming that they’ll only see their own data! They could simply change the filter at the top, and see any of the other data.

The best way to use these copies of the pivot table is to group the sheets, and then print them, or create PDF files. That will give you a quick report that you can share with others, without them having access to the underlying data.

Feature Quirks and Limitations

Here are a few quirks and limitations to keep in mind when you use the Show Report Filter Pages feature.

Selected Items:

• If “(All)” is selected in the Report Filter, a new copy of the pivot table is created for each item
• If one item is selected, and Select Multiple Items is ON, only that item will have a pivot table copy created.
• If one item is selected, and Select Multiple Items is OFF, all items will have a pivot table copy created, EXCEPT the selected item – the original pivot table shows that item’s data.
• If two or more items are selected (Select Multiple Items), only the selected items will have a pivot table copy created.

Item Names:

• If the item contains characters that aren’t allowed in sheet names, such as an asterisk, a numbered sheet name is used instead
• If the item name is longer than 31 characters (max allowed for sheet names), a numbered sheet name is used instead
• If the item name has 31 or fewer characters, the first 26 characters are used as the sheet name.

Copy Pivot Table Sheet Macro

The Show Report Filter Pages feature only copies the pivot table, and nothing else from the original sheet. If you want a copy of the entire sheet for each item in the Report Filter, there is a macro on my Contextures site that you can use.

Go to the Pivot Table Report Filter Macros page, and look for the “Create New Sheet for Each Item in Report Filter” section.

The macro makes a copy of the entire worksheet for each item, and adds a prefix to each sheet’s name. That makes the sheets easy to locate and delete later.

There is a sample file that you can download, and it has a pivot table and the macro, so you can test it there, before trying the macro in your own workbook.

___________________________