How to export a PivotTable to a new workbook using VBA?

Exporting a PivotTable to a new workbook using VBA involves several steps. You’ll need to create a new workbook, copy the PivotTable, and paste it into the new workbook. Here’s a step-by-step guide with sample VBA code:

Sub ExportPivotTableToNewWorkbook()
    Dim wsSource As Worksheet
    Dim pt As PivotTable
    Dim wbNew As Workbook
    Dim wsNew As Worksheet
    
    ' Set the source worksheet and pivot table
    Set wsSource = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet's name
    Set pt = wsSource.PivotTables("PivotTable1") ' Change "PivotTable1" to your pivot table's name

    ' Add a new workbook
    Set wbNew = Workbooks.Add
    Set wsNew = wbNew.Sheets(1)
    
    ' Copy the entire PivotTable to the new sheet
    pt.TableRange2.Copy
    wsNew.Range("A1").PasteSpecial Paste:=xlPasteValues
    wsNew.Range("A1").PasteSpecial Paste:=xlPasteFormats
    
    ' Optional: Save the new workbook
    'wbNew.SaveAs Filename:="C:PathToYourFolderPivotTableExport.xlsx"
    
    ' Clean up
    Application.CutCopyMode = False
    MsgBox "PivotTable exported successfully to a new workbook.", vbInformation
End Sub

Open the VBA Editor:

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

Insert a New Module:

Right-click on any of the items in the Project Explorer, choose `Insert`, and then `Module`.

Write the VBA Code:

Copy and paste the following code into the module:

Customize the Code:

Replace `”Sheet1″` with the name of the sheet containing your PivotTable.
Replace `”PivotTable1″` with the name of your PivotTable. You can find the name of the PivotTable by selecting it and looking at the PivotTable Analyze (or Options in older Excel versions) tab.

Run the Macro:

Close the VBA editor.
Go to `Developer` tab, click on `Macros`, select `ExportPivotTableToNewWorkbook`, and click `Run`.

Note: The code above copies the PivotTable as values and formats, so it will not be interactive as a PivotTable in the new workbook; instead, it will be static data. If you need the PivotTable functionality, you would need to copy the relevant data source along with creating a new PivotTable in the new workbook.

facebooklinkedin