Copy and paste a pivot table, to keep its values and formatting. Do the steps manually, or with a macro.
When you try to copy a pivot table's formatting and values to another location, without the underlying data, you might run into problems.
In Excel 2007 and newer versions, if you paste the copy with the Values and Source Formatting command, the pivot table's Style formatting is not pasted -- just the values, and any normal formatting that you had applied, such as Fill colour from the Home tab.
The instructions below show how to successfully copy a pivot table's formatting and values to another location, either manually or with a macro.
To manually copy pivot table values and formatting, use one of the techniques shown below. If your pivot table does NOT have any fields in the Filters area, use method 1 or 3. For pivot tables with report filters, use method 2 or 3.
If the pivot table does NOT have any report filters, follow these steps to manually copy and paste the pivot table formatting and values. Thanks to Jeffrey Browne for this tip.
If the pivot table has one or more report filters, the technique shown above does not work. Use this two-step technique instead. Thanks to Loh HW for suggesting this technique.
To manually copy and paste the pivot table formatting and values, follow these steps:
NOTE: This technique will not work if you select any cells outside of the pivot table, or blank cells between Report filter fields, horizontally arranged (as shown below).
Instead of using Excel's paste commands, you can use the Office Clipboard to paste the pivot table values and style formatting. Thanks to John Walkenbach for sharing this tip.
NOTE: This technique works for pivot tables with or without report filters. It also works if there are spaces between the report filters, or if you copy blank cells outside of the pivot table.
Follow these steps to copy a pivot table's values and formatting:
The following macro will copy pivot table values and formatting, and works for pivot table with or without report filters.
The macro 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 multiple Report filter fields, horizontally arranged. In that case, you'll see a message at the end of the macro, to let you know about the problem.
Copy the following code to a regular code module in Excel. Then, select a pivot table cell, and run the macro.
Sub PivotCopyFormatValues() 'select pivot table cell first 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 Dim msgSpace As String 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 End If If pt.PageFieldOrder = xlOverThenDown Then If pt.PageFields.Count > 1 Then msgSpace = "Horizontal filters with spaces." _ & vbCrLf _ & "Could not copy Filters formatting." End If End If 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) If Not rngPTa Is Nothing Then lRowPage = rngPTa.Rows(1).Row rngPTa.Copy Destination:=ws.Cells(lRowPage, 1) End If ws.Columns.AutoFit If msgSpace <> "" Then MsgBox msgSpace End If exitHandler: Exit Sub errHandler: MsgBox "Could not copy pivot table for active cell" Resume exitHandler End Sub
Get the Copy Pivot Table Values and Formatting sample file, with a pivot table for testing, and the macro from this page. The zipped file is in xlsb format (Binary), and be sure to enable macros when you open the workbook, if you want to try the macro.
Last updated: April 17, 2019 4:31 PM
Contextures RSS Feed