How to Copy and Paste Pivot Table Values With Formatting VBA

Posted on by Tomasz Decker

When working with pivot tables in Excel, you may often need to copy the values and formatting to another location. This could be for presentation purposes, or when you need to further process the data without the pivot table structure.

Using Visual Basic for Applications (VBA), Excel’s programming language can automate this task. In this tutorial, you will learn how to write a VBA macro that copies a pivot table’s values and formatting and pastes them to a specified location.

Example

This pivot table shows the total salary and the count of names for each department. You can experiment with different arrangements of fields in the PivotTable Fields pane to analyze your data in various ways.

Steps to Create the VBA Macro

To begin, it is important to understand that the macro will require reference to specific objects within Excel, such as the pivot table and the destination range. You’ll also use the PasteSpecial method to ensure that both values and formats are carried over.

1. Identify the Pivot Table and Destination Range

First, assign the pivot table and the destination range to variables. It’s essential to specify the correct sheet and pivot table name to avoid errors. Here’s how you do it:

Dim ws As Worksheet
Dim pt As PivotTable
Dim rngDestination As Range

Set ws = ThisWorkbook.Sheets("YourSheetName") ' Change to your sheet name
Set pt = ws.PivotTables("YourPivotTableName") ' Change to your pivot table name
Set rngDestination = ws.Range("YourDestinationCell") ' Change to your starting cell of destination range

2. Copy the Pivot Table

Next, copy the entire pivot table including formatting.

pt.TableRange2.Copy

3. Paste Values and Formatting to the Destination

With the pivot table copied to the clipboard, use the PasteSpecial method to paste the values and then the formats separately into the destination range.

With rngDestination
    ' Paste values
    .PasteSpecial Paste:=xlPasteValues
    ' Paste formats
    .PasteSpecial Paste:=xlPasteFormats
End With

Application.CutCopyMode = False ' Clear the clipboard

Final Result

The Full VBA Code

Below is the full VBA code. It will copy your pivot table’s values and formatting to the specified destination. Remember to customize the sheet name, pivot table name, and destination range to match your Excel workbook.

Sub CopyPivotTableWithFormatting()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim rngDestination As Range

    ' Set references to your worksheet, pivot table and destination range
    Set ws = ThisWorkbook.Sheets("Sheet2") ' Change to your sheet name
    Set pt = ws.PivotTables("PivotTable1") ' Change to your pivot table name
    Set rngDestination = ws.Range("D3") ' Change to your starting cell of destination range

    ' Copy the pivot table
    pt.TableRange2.Copy

    ' Paste values and formats into destination range
    With rngDestination
        .PasteSpecial Paste:=xlPasteValues
        .PasteSpecial Paste:=xlPasteFormats
    End With

    ' Clear the clipboard
    Application.CutCopyMode = False
End Sub

Conclusion

Automating tasks like copying and pasting pivot table values and formatting into a new location can greatly improve your productivity in Excel. By following the steps above and utilizing the provided VBA macro, you can streamline your Excel workflow, ensuring accurate replication of the data and cell formatting. Take advantage of this powerful feature to manage your data more efficiently.

[wpforms id="17428"]
Posted in vba