How to copy a PivotTable to another sheet using VBA?

Copying a PivotTable to another sheet using VBA involves several steps, including selecting the PivotTable, copying it, and then pasting it onto the target sheet. Below is a guide on how to achieve this:

Sub CopyPivotTableToAnotherSheet()
    Dim wsSource As Worksheet
    Dim wsDestination As Worksheet
    Dim pt As PivotTable
    Dim rngDestination As Range

    ' Set the source worksheet and pivot table
    Set wsSource = ThisWorkbook.Sheets("Sheet1")
    Set pt = wsSource.PivotTables("PivotTable1")  ' Adjust the name as necessary

    ' Set the destination worksheet
    Set wsDestination = ThisWorkbook.Sheets("Sheet2")
    
    ' Specify where you want to paste the PivotTable
    Set rngDestination = wsDestination.Cells(1, 1)  ' Change as necessary

    ' Copy the entire pivot table including the data, format, and layout
    pt.TableRange2.Copy

    ' Paste into the destination
    rngDestination.PasteSpecial Paste:=xlPasteAll

    ' Cleanup
    Application.CutCopyMode = False
End Sub

Open the Visual Basic for Applications (VBA) Editor:

Press `ALT` + `F11` in Excel to open the VBA editor.

Insert a New Module:

Right-click on any of the workbook objects in the Project Explorer.
Choose `Insert` > `Module` to create a new module.

Write the VBA Code:

You can use the following VBA code as a basis to copy a PivotTable from one sheet to another:

Customize the Code:

Replace `”Sheet1″` and `”PivotTable1″` with the actual names of your source sheet and PivotTable.
Modify `”Sheet2″` and `rngDestination` to match your target sheet and desired starting cell for the copied PivotTable.

Run the Macro:

Press `F5` in the VBA editor to run your macro, or close the editor and run it from Excel via `Developer` > `Macros`.

Important Considerations:

PivotTable Names and Ranges: Ensure that you have the correct names and ranges.
Destination Sheet and Ranges: Ensure the destination sheet is ready and the anchor cell (`rngDestination`) is set to the correct starting point.
Macros Settings: Make sure that macros are enabled in Excel to run the VBA code.

This code will copy the entire PivotTable, preserving the data, format, and layout from the source sheet to the destination sheet. Adjust the cell references and worksheet names as needed for your specific use case.

facebooklinkedin