Search Contextures Sites ![]()
Excel VBA Pivot Table Paste Format and Values
Problems Copying PivotTable Styles Formatting
Manually Copy Pivot Table Values and Format
Copy Pivot Table Values and Format With VBA
Pivot Table Tutorials and Videos
Problems Copying PivotTable Styles Formatting
In Excel 2003, and earlier versions, you can copy a pivot table, and use the Paste Special command to paste its values and formatting in a different location.
However, if you create a pivot table in Excel 2007 and later versions, the formatting is applied by using the PivotTable Styles feature.
When you copy an Excel 2007 or later pivot table, and paste it with the Paste Special command, the PivotStyle formatting is not pasted.
Fortunately, there are workarounds for this problem:
- Use the Office Clipboard, to paste the Pivot Table formatting
- Copy and paste the pivot table in a couple of steps, as shown below
- Programmatically copy the pivot table in a couple of steps
Manually Copy Pivot Table Values and Format
You can copy and paste the pivot table values and formatting without the Office Clipboard, if you perform the task in a couple of steps, instead of all at once.
Thanks to Loh HW for suggesting this technique.
For a Pivot Table With Report Filters
To manually copy and paste the pivot table formatting and values, follow these steps:
- In the original pivot table, copy the Report Filter labels and fields
- Switch to the location where you want to paste the pivot table values and formatting
- Click the Paste button on the Ribbon (or use a paste shortcut) to paste the copied Report Filters
- In the original pivot table, copy the pivot table body (row labels, column labels, values)
- Switch to the location where you want to paste the pivot table values and formatting
- Paste the copied pivot table body
NOTE: This technique will not work if you select any cells outside of the pivot table, or blank cells between horizontal report filters (as shown below).
For a Pivot Table Without Report Filters
To manually copy and paste the pivot table formatting and values, follow these steps:
- In the original pivot table, copy the pivot table body (row labels, column labels, values), except for the last row
- Switch to the location where you want to paste the pivot table values and formatting
- Paste the copied pivot table body
- In the original pivot table, copy last row of the pivot table body
- Switch to the location where you want to paste the pivot table values and formatting
- Paste the copied last row of the pivot table body
NOTE: This technique will not work if you select any cells outside of the pivot table.
Copy Pivot Table Values and Format With VBA
The following code adds a new sheet, then it copies the selected pivot table's values and formatting to the new worksheet. The pivot table is copied to column A, in the same row as the existing pivot table.
NOTE: Report filter formatting will not be copied if there are horizontally arranged report filters.
Sub PivotCopyFormatValues() Dim ws As Worksheet Dim pt As PivotTable Dim rngPT As Range Dim rngPTa As Range Dim rngCopy As Range Dim rngCopy2 As Range Dim lRowTop As Long Dim lRowsPT As Long Dim lRowPage As Long On Error Resume Next Set pt = ActiveCell.PivotTable Set rngPTa = pt.PageRange On Error GoTo errHandler If pt Is Nothing Then MsgBox "Could not copy pivot table for active cell" GoTo exitHandler Else Set rngPT = pt.TableRange1 lRowTop = rngPT.Rows(1).Row lRowsPT = rngPT.Rows.Count Set ws = Worksheets.Add Set rngCopy = rngPT.Resize(lRowsPT - 1) Set rngCopy2 = rngPT.Rows(lRowsPT) rngCopy.Copy Destination:=ws.Cells(lRowTop, 1) rngCopy2.Copy Destination:=ws.Cells(lRowTop + lRowsPT - 1, 1) End If If Not rngPTa Is Nothing Then lRowPage = rngPTa.Rows(1).Row rngPTa.Copy Destination:=ws.Cells(lRowPage, 1) End If ws.Columns.AutoFit exitHandler: Exit Sub errHandler: MsgBox "Could not copy pivot table for active cell" Resume exitHandler End Sub
![]()
![]()
Contextures Inc., Copyright ©2011
All rights reserved.
Last updated: November 18, 2010