Search Contextures Sites


Amazon.com 

 

 

 

 

 

 

Learn how to create Excel dashboards.

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.

pivot styles

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:

  1. Use the Office Clipboard, to paste the Pivot Table formatting
  2. Copy and paste the pivot table in a couple of steps, as shown below
  3. 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:

  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.

  

 

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

     
     

 

 

 Learn how to create Excel dashboards.
 

 

  

Pivot Table Tutorials

Pivot Table Introduction 
Clear Old Items in Pivot Table
Create a Pivot Table in Excel 2007 
Custom Calculations 
Data Field Layout
Dynamic Data Source
FAQs - Pivot Tables
Field Settings
Filter Source Data  
Filters, Top 10 
Filters, Report Filters
GetPivotData Function
Grand Totals
Grouping Data
Layout, Excel 2007
Multiple Consolidation Ranges
Pivot Cache   
PivotTable Style
Printing a Pivot Table   
Protection  
Running Totals  
Show and Hide Items 
Sorting
Subtotals 
Summary Functions
Unique Item Count

Pivot Table Books

Beginning Pivot Tables (Excel 2007) 
Pivot Tables Recipe Book (Excel 2003) 
Pivot Tables Recipe Book (Excel 2007) 

Pivot Table Add-Ins

Pivot Power 
Pivot Play PLUS 

Pivot Table Videos

Clear Old Items
Copy a Custom PivotTable Style
Create Pivot Table in Excel 2007
Create Pivot Table from Multiple Sheets
Data Field Layout
Date Filters, Add
GetPivotData
Group Data
Layout, Excel 2007
Report Filters, Add
Running Totals
Select Sections
Subtotals, Create Multiple
Top 10 Filters

 

 

Privacy Policy

 

Contextures Inc., Copyright ©2013
All rights reserved.

 

Last updated: May 21, 2013