Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


How To Create Sheets For Each Item In PivotTable Field

Create WS for each item in PT

Some Excel features hide in plain sight. They are there, but nobody thinks to click the button. In this post, we are covering one of those features. We are looking at how to create separate sheets for each item in a PivotTable field with the Show Report Filter Pages feature.

Excel users love PivotTables because they are a quick and easy method for summarizing data without any formulas. Often the underlying data includes different cost centers, managers, or departments. Therefore, it’s common to duplicate the PivotTable and change the filters so that each item has a separate tab with its own view.

Separate Worksheet for each Item - Example

STOP! Don’t go any further with this manual work. PivotTables have a secret hidden feature called Show Report Filter Pages, which automatically creates those views for us.

Download the example file: Click the link below to download the example file used for this post:

Watch the Video

Watch the video on YouTube

Show Report Filter Pages

The Show Report Filter Pages feature is found in PivotTable Analyze > Options > Show Report Filter Pages…

For each unique item in the Pivot field:

  • A new worksheet is created containing the Pivot Table.
  • The worksheet is named based on the field value
  • The PivotTable is filtered to the field value.

It doesn’t matter how many items we have; it will create a report for each of them. If we have a lot, it might take quite a while to run and probably won’t give the best user experience. But, it can, if necessary.

Let’s dig a bit deeper into this feature to understand how it works.

Setup: Add the fields to the Filter section

I’m assuming you’ve already got a PivotTable created. Identify the field you wish to create individual worksheets for. Add that field to the Filters section.

Filters Section to expand the Region

As shown above, we’ve added the Region field to the Filters section.

Note: This technique only works with items in the Filters section.

Execute: Run the Short Report Filter Pages

Before doing anything else… make sure the PivotTable is exactly how you want it. The current style, layout, and existing filters are replicated for each worksheet created.

  1. Select a cell in the PivotTable to reveal the PivotTable Analyze ribbon.
  2. Click PivotTable Analyze > Options > Show Report Filter Pages…
    How to Run Show Report Filter Pages
  3. The Show Report Filter Pages dialog box appears. Select the field to create the reports for.
  4. Click OK.
    Show Report Filter Pages dialog box

BOOM! The worksheets are created with unique names, and the PivotTables are pre-filtered for each item.

Notes

There are a few things to be aware of when using this feature.

Shared Pivot Cache

The PivotTables all share the same Pivot Cache. Therefore, refreshing data for one PivotTable, updates all the other PivotTables.

Changes are not shown in all PivotTables

While data is shared between PivotTables, any other changes are not. Each PivotTable is a separate object and does not share formatting or layouts. This is why it’s essential to get it right at the start.

If you wish to change lots of PivotTables, it is easier to delete all the additional sheets and re-generate them using the same process.

Other objects are not copied?

The feature creates a new sheet and adds a PivotTable to it. Therefore, any other objects on the master worksheet are not copied.

Even though you may consider them part of the PivotTable, slicers, and PivotCharts are not copied.

Sheet Names

The sheets are created in alphabetical order.

If a worksheet with that name already exists, the new worksheet will be created with a number (e.g., Sheet(1))

What about Data Model PivotTables?

Show Report Filter Pages works for standard PivotTables only. Any PivotTables created using Power Pivot or a connected Data Model do not have this feature.

Using multiple Filter fields?

Before you ask… no, this doesn’t work with multiple Filter fields. The Show Report Filter Pages dialog box only allows a single field selection.

Nice try though 😁

Copying all worksheet objects

We noted above that Show Report Filter Pages does not copy all the sheet objects. So if we want to copy those, we must turn to a VBA Macro, or an Office Script.

VBA Code

Copy the following into a standard code module. Select the sheet with the PivotTable, then run the code.

An input box asks us to enter the name of the field to use.

The code comments provide additional detail about each section.

Sub pivotTableWsCopy()

Dim ws As Worksheet
Dim newWs As Worksheet
Dim pvt As PivotTable
Dim newPvt As PivotTable
Dim pvtField As PivotField
Dim newPvtField As PivotField
Dim pvtFieldName As String
Dim pvtItem As PivotItem

'On Error Exit Sub, not a PT page
On Error Resume Next
Set ws = ActiveSheet
Set pvt = ws.PivotTables(1)
If Err.Number <> 0 Then Exit Sub

'Capture the name of the Pivot Field
pvtFieldName = Application.InputBox(Prompt:="Apply based on which field?", Title:="PivotTable", Type:=2)

'Escape if user clicks Cancel
If pvtFieldName = "False" Then Exit Sub

'Set the Pivot Field
On Error Resume Next
Set pvtField = pvt.PivotFields(pvtFieldName)
If Err.Number <> 0 Then
    MsgBox pvtFieldName & "does not exist in the PivotTable"
    Exit Sub
End If

'Turn off message when deleting sheet
Application.DisplayAlerts = False

'Delete any existing PT sheets
For Each pvtItem In pvtField.PivotItems
    On Error Resume Next
    Sheets(pvtItem.Name).Delete
    On Error GoTo 0
    
    'Copy the worksheet
    ws.Copy After:=ActiveWorkbook.Sheets(Sheets.Count)
    Set newWs = ActiveWorkbook.Sheets(Sheets.Count)
    newWs.Name = pvtItem.Name
    
    'Change the PivotItem
    Set newPvt = newWs.PivotTables(1)
    Set newPvtField = newPvt.PivotFields(pvtFieldName)
    newPvtField.ClearAllFilters
    newPvtField.CurrentPage = pvtItem.Name
    
Next

'Turn on alerts
Application.DisplayAlerts = True

'Select the worksheet
ws.Activate

End Sub

Office Script

We also have an Office Scripts solution.

Copy the following code into the script editor. Select the sheet with the PivotTable, then run the script.

The execution is similar to the VBA method, but since Office Scripts don’t have an easy way to display a form for user input, it uses the first field in the Filters section.

The comments provide additional detail about each section.

function main(workbook: ExcelScript.Workbook) {

  let ws = workbook.getActiveWorksheet();
  let newWs: ExcelScript.Worksheet;
  let pvt: ExcelScript.PivotTable;
  let newPvt: ExcelScript.PivotTable;
  let pvtItemName: string;

  //On Error return, not a PT page
  try {
      pvt = ws.getPivotTables()[0];
  } catch {
      return;
  }

  //Get the Field Name
  let pvtField = pvt.getFilterHierarchies()[0].getFields()[0];
  let pvtFieldName = pvtField.getName();

  //Loop through all Items in Field
  for (let i = 0; i < pvtField.getItems().length; i++) {

    //Get the name of the Item
    pvtItemName = pvtField.getItems()[i].getName();

    //Delete worksheet if exists
    try {
      newWs = workbook.getWorksheet(pvtItemName);
        newWs.delete();
    } catch {};

    //Create the new worksheet
    newWs = ws.copy(ExcelScript.WorksheetPositionType.after, ws);
    newWs.setPosition(workbook.getWorksheets().length - 1);
    newWs.setName(pvtItemName);

    //Change PivotTable Item
    newPvt = newWs.getPivotTables()[0];
    newPvt.getFilterHierarchy(pvtFieldName).
      getPivotField(pvtFieldName).applyFilter({
        manualFilter: {
          selectedItems: [pvtItemName]
        }
      });
  }
}

Conclusion

In this post, we’ve seen how to create separate sheets for each item in a PivotTable field. All it takes is a few clicks and the Show Report Filter Pages feature does all the work for us.

However, if you wish to copy other worksheet objects, the VBA and Office Scripts may better suit your needs.

Related Posts:


Headshot Round

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).


Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise.  List all the things you've tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

Leave a Reply

Your email address will not be published. Required fields are marked *