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:
|
1 2 3 4 5 6 7 |
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.
|
1 |
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.
|
1 2 3 4 5 6 7 8 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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.