Excel Pivot Table Macro Paste Format Values - Contextures

• Topics
  • Charts
  • Data Entry
  • Data Validation
  • Filters
  • Formatting
  • Formulas
  • Macros
  • Pivot Tables
  • Sample Data
  • Sample Files
  • Tutorial Index

Home > Pivot > Layout > Copy Formatting Copy Pivot Table Formatting Copy and paste a pivot table, to keep its values and formatting, without the source data. Do the steps manually, or with a macro, to create an unlinked pivot table, with its style formatting.

Video: Copy Pivot Table Formatting

Problems Copying Pivot Table Formatting

Copy Pivot Table Format - Manual

Copy Pivot Table Format - Macro

Video Transcript

Get the Sample File

More Tutorials and Videos

Copy PivotTable Style Format and Values

When you try to copy the pivot table formatting and values to another location, the PivotTable Style formatting isn't copied. This video shows how to manually copy the formatting from a pivot table. Written instructions are below the video.

There is also a macro to copy pivot table values and formatting, which makes the task easier.

The full Video Transcript is at the bottom of this page

Problems Copying Pivot Table Formatting

When you try to copy a pivot table's formatting and values to another location, without the underlying source 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 PivotTable 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.

Copy Pivot Table Format - Manual

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.

1. Manual steps - no report filters
2. Manual steps - with report filters
3. Manual steps - with or without report filters

Manual - No Report Filters

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.

1. In the original pivot table, copy the entire pivot table body Note: Do not include cells that are outside of the pivot table

   

2. Right-click the cell where you want to paste the values and formatting
3. Under Paste Options, click Values



4. Right-click the cell again, and under Paste Options, click Formatting



5. The values and formatting appear in the new location.

Manual - With Report Filters

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:

1. In the original pivot table, copy the Report Filter labels and fields only Note:: Do NOT include any blank cells



2. Select the cell where you want to paste the values and formatting
3. Press Ctrl + V to paste the Report Filters



4. Go back to the original pivot table, and copy the entire pivot table body - do not copy any cells outside of the pivot table.



5. Select the cell where you want to paste the pivot table body values and formatting
6. Press Ctrl + V to paste the pivot table body



7. The values and formatting appear in the new location.

▶	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)

Manual - With or Without Report Filters

Instead of using Excel's paste special commands, you can use the Office Clipboard to paste the copy of a 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 in their layout. 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:

1. Select the original pivot table, and copy it.
2. Click the cell where you want to paste the copy.
3. On the Excel Ribbon's Home tab, click the Dialog Launcher button in the Clipboard group .



4. In the Clipboard, click on the pivot table copy, in the list of copied items..



5. The pasted copy has the values and style formatting from the original pivot table. ▲TOP

Copy Pivot Table Format - Macro

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.

Horizontal Report Filters

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.

• "Horizontal filters with spaces. Count not copy Filters formatting"

Click OK to close the message

• 

Add Code to Workbook

Copy the following VBA 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

Video Transcript: Copy Pivot Table Format & Values

Here is the full transcript for the video - Copy Pivot Table Format and Values, at the top of this page

'-----------------------------

Introduction

When you create a pivot table in Excel, you can change its appearance by using a pivot table style.

So right now, this pivot table has the default style, which is quite plain.

And I'm going to the pivot table tools tab and I'll click design tab, and there's a menu of pivot table styles.

So I'll select something, maybe something from the dark category.

So this one, the preview shows me has a dark heading and a row at the bottom with some blue in the centre.

So I'll select that.

Copy and Paste Pivot Table

Now, if I try and copy this pivot table, so I'm just getting the values, but not the underlying data.

So I want to change this just into values instead of a real pivot table.

When I highlight columns A and B, and then copy them,

I'll click at the top of column B and I don't want to paste. I want to just paste the values.

So from this drop down, I'll paste the values.

The other option is this Values and Source Formatting.

When I click that, we can see that I get the values from the pivot table, but not the pivot table style that I applied.

Different Way to Copy and Paste

So to delete this, I'll just delete those rows and to try a different way of copying and pasting a pivot table so we can get the formatting.

I'll again, select those columns and copy

Click, where I'd like to paste it. But instead of using the paste button here, I'm going to open the clipboard.

So I'll click this dialogue launcher, and here's the clipboard.

And you can see the item that I just copied.

And when I click that, it will paste the pivot table values and the formatting, not the column widths, however, I'd have to adjust those manually.

Pivot Formatting & Values Pasted

But when I click here, you can see the pivot table field list appears. So is a pivot table.

When I click here, there's no pivot table field list.

We have all the values and the formatting, but it's not a pivot table anymore.

So none of my underlying source data would be disclosed, if I copied this now and sent it to someone.

Download the Sample File

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.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Subscribe.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

More Tutorials

FAQs - Pivot Tables

Excel Slicer Macros

Pivot Table Introduction

Grouping Data

Multiple Consolidation Ranges

Running Totals

Summary Functions

Clear Old Items in Pivot Table

Pivot Table Intro

Grouping Data

Last updated: June 3, 2024 10:06 AM