How to Copy and Paste Pivot Table Values With Formatting VBA

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.


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:

2. Copy the Pivot Table

Next, copy the entire pivot table including formatting.

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.

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.


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.

Posted in vba