VBA & Macro Problems

How to Create Detail Worksheets for Excel PivotTable Rows with VBA

Camila Milosovich Oct 22, 2026 869 views

Question details

The user needs to automate the creation of detail worksheets for specific PivotTable rows (e.g., employee and status) using a VBA macro, while implementing a custom naming convention to handle duplicates.

Create Detail Worksheets for Excel PivotTable Rows Using VBA

Product: Excel
Device & OS: not provided
Scenario: Extracting detailed data from an Excel PivotTable for specific employees and statuses into separate, properly named worksheets.
Observed behavior: The user wants to generate multiple detail sheets (e.g., 22 sheets for 11 employees across 2 statuses) named after the employee, with a numeric suffix for duplicate names, excluding total rows.

Before you start

Ensure that your workbook contains the source data and the PivotTable, and save a backup copy of your file as an Excel Macro-Enabled Workbook (.xlsm) before running new VBA code.

Solution 1Recommended

Use a VBA Macro to Generate and Name Detail Sheets

Implement a structured VBA script to loop through PivotTable fields, extract details using the ShowDetails method, and rename the resulting worksheets while safely handling duplicate names.

This solution uses structured PivotTable and worksheet object references to filter the required fields and create the detail sheets. It generates valid worksheet names and automatically appends a numeric suffix (e.g., 'EmployeeName 1') if a sheet with that name already exists.

It is highly recommended to avoid using 'On Error Resume Next' as a blanket error handler in your main procedure, as it can hide unexpected issues during the sheet creation process. Instead, use specific error handling or string validation for naming.

Open the VBA Editor

Open your Excel workbook, press Alt + F11 to launch the VBA Editor, and go to Insert > Module to create a new script area.

Reference the PivotTable Object

Write a VBA script that references your specific PivotTable object, for example, using 'Set pt = ActiveSheet.PivotTables("PivotTable1")'.

Loop Through Target Cells

Set up a loop to iterate through the specific PivotField items (like employees and statuses) in the PivotTable's DataBodyRange, adding a condition to skip any 'Grand Total' or 'Subtotal' rows.

Extract Details

Trigger the detail extraction by using 'TargetCell.ShowDetail = True', which forces Excel to create a new worksheet with the underlying row data.

Rename Sheets with Duplicate Handling

Capture the newly created ActiveSheet and rename it using the employee's name. Include a Loop that checks existing worksheet names in the workbook to append a suffix (e.g., ' 1') if a duplicate name is detected.

Run and Sort

Run the macro by pressing F5. Optionally, add a sorting subroutine at the end of your script to arrange the newly generated sheets in alphabetical order using the 'Worksheet.Move' method.

Testing with Sample Data: Before running the script on your full dataset, test it on a small, anonymized sample workbook to validate that the exact number of detail sheets (e.g., 22 sheets) is correctly generated and properly named.

Run PivotTable Macros and Manage Data in WPS Spreadsheet

WPS Office provides robust support for macros, PivotTables, and advanced data processing. You can seamlessly write, run, and manage your VBA scripts directly within WPS Spreadsheet to automate repetitive tasks like generating detail worksheets.

1. Enable Macros in WPS: Open WPS Spreadsheet, navigate to the 'Developer' tab on the ribbon, and ensure Macros are enabled for your current session.
2. Open the VBA Editor: Click on 'Visual Basic' in the Developer tab or press Alt + F11 to launch the macro editor.
3. Insert Your Script: Go to Insert > Module and paste your PivotTable detail extraction VBA code into the editor window.
4. Run and Automate: Execute the macro to automatically generate, properly name, and organize your detail worksheets based on your PivotTable data without relying on manual extraction.

Fully compatible with Microsoft Excel (.xlsx and .xlsm) formats.Built-in macro editor supporting both VBA and JSA (JavaScript for Applications).Advanced PivotTable features to summarize and extract detailed data effortlessly.Free to use with a lightweight installation and familiar user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a naming error when creating multiple detail sheets with VBA?

Excel requires each worksheet to have a unique name up to 31 characters. If your macro tries to name a new sheet with an employee name that already exists, it will throw a run-time error. You must include logic in your VBA code to check existing names and append a numerical suffix for duplicates.

How do I exclude 'Grand Total' rows from my VBA detail extraction?

In your VBA loop, you can check the '.RowRange' or '.PivotItems' properties of the PivotTable. Add an 'If' statement to verify that the item name does not equal 'Grand Total' or check if the target cell belongs to a subtotal row before executing the 'ShowDetail = True' command.

What is the specific VBA command to show PivotTable details?

The core VBA command is 'Range.ShowDetail = True'. When this property is applied to a specific data cell within the PivotTable's DataBodyRange, Excel mimics the double-click action and automatically creates a new worksheet containing the underlying rows for that value.

Can I automatically sort the newly created detail sheets?

Yes. After your macro finishes generating all the detail worksheets, you can write a separate loop or sorting algorithm within VBA to iterate through the sheets and move them into alphabetical order using the 'Worksheet.Move' method.

Excel & Data Problems