Contextures

Copy Pivot Table Formatting

Copy and paste a pivot table, to keep its values and formatting. Do the steps manually, or with a macro.

Problems Copying Pivot Table Formatting

When you copy an Excel pivot table, and paste it with the Paste Special command, the pivot table's formatting is not pasted, if that formatting was applied by using a PivotTable Style.

Fortunately, there are workarounds for this problem:

  1. Copy and paste with Office Clipboard
  2. Copy and paste in two steps (manually)
  3. Copy and paste in two steps (macro)

Older Versions of Excel

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.

Unfortunately, that function stopped working when PivotTable Styles were introduced in Excel 2007.

pivot styles

Copy Pivot Table Format Manually

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:

  1. In the original pivot table, copy the Report Filter labels and fields
  2. Switch to the location where you want to paste the pivot table values and formatting
  3. Click the Paste button on the Ribbon (or use a paste shortcut) to paste the copied Report Filters
  4. In the original pivot table, copy the pivot table body (row labels, column labels, values)

    pivot table body copy

  5. Switch to the location where you want to paste the pivot table values and formatting
  6. 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).

horizontal report filters

For a Pivot Table Without Report Filters

To manually copy and paste the pivot table formatting and values, follow these steps:

  1. In the original pivot table, copy the pivot table body (row labels, column labels, values), except for the last row

    pivot copy body

  2. Switch to the location where you want to paste the pivot table values and formatting
  3. Paste the copied pivot table body
  4. In the original pivot table, copy last row of the pivot table body

    pivot copy last row

  5. Switch to the location where you want to paste the pivot table values and formatting
  6. 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. go to top

Copy Pivot Table Format - Macro

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.go to top

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

go to top

More Tutorials

FAQs - Pivot Tables

Pivot Table Introduction

Grouping Data

Multiple Consolidation Ranges

Running Totals

Summary Functions

Clear Old Items in Pivot Table

Search Contextures Sites

 

Excel Tools Add-in

Free Pivot Table Tools

 

Pivot Power Premium

 

Excel Data Entry Popup List

 

 

 

Excel UserForms for Data Entry

 

Last updated: September 11, 2016 6:28 PM
Contextures RSS Feed