Search Contextures Sites

Excel VBA Pivot Table Paste Format and Values

Amazon.com      Amazon.com	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

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

Pivot Table Books Pivot Tables, Beginning (Excel 2007)  Pivot Tables, Recipe Book (Excel 2003)  Pivot Tables, Recipe Book (Excel 2007)  Pivot Table Add-Ins Pivot Tables - Add-in -- Pivot Power  Pivot Tables - Add-in - Pivot Play PLUS  Pivot Table Videos Pivot Tables - Clear Old Items Pivot Tables - Copy a Custom PivotTable Style Pivot Tables - Create in Excel 2007 Pivot Tables - Create from Multiple Sheets Pivot Tables - Data Field Layout Pivot Tables - Date Filters, Add Pivot Tables - GetPivotData Pivot Tables - Group Data Pivot Tables - Layout, Excel 2007 Pivot Tables - Report Filters, Add Pivot Tables - Running Totals Pivot Tables - Select Sections Pivot Tables - Subtotals, Create Multiple Pivot Tables - Top 10 Filters

 

Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact
Privacy Policy		Contextures Inc., Copyright ©2011 All rights reserved.	Debra Dalgleish

Last updated: November 18, 2010