Excel - Copying pivot tables on multiple sheets and pasting as values (retaining formatting)

Question

here was my project:

I created a pivot table from a central Excel file on item pricing and data. I did "show filter pages" to create different worksheets for each unique entry in a particular field (created in excess of 100 worksheets). I moved all the resulting PivotTable sheets to their own workbook (titled PivotTableResults).

What I want to do is automate the copying of the PivotTable data, then paste as values in the next available blank row below the PivotTable. Then paste the same PivotTable again to retain the formatting, for ALL worksheets in the book.

I followed this advice on accomplishing the PivotTable pasting values/formatting: http://spreadsheetpage.com/index.php/tip/unlinking_a_pivot_table_from_its_source_data/

Here's my current code:

Application.ScreenUpdating = False

Dim ws As Worksheet
    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables(1)

For Each ws In ActiveWorkbook.Worksheets
    Dim NextRow As Range
    Set NextRow = ws.Cells(Cells.Rows.Count).End(xlUp).Offset(1)
    For Each pt In ws.PivotTables
        'ws.PivotTables("pt").PivotSelect "", xlDataAndLabel, True
        pt.TableRange2.Copy
        Set CurrentRow = NextRow
        CurrentRow.Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
        CurrentRow.Select
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Next pt

Next ws
End Sub

Any suggestions?

What I want to do is automate the copying of the PivotTable data, then paste as values in the next available blank row below the PivotTable. Then paste the same PivotTable again to retain the formatting, for ALL worksheets in the book. My VBA code is ngiving me multiple areas, not sure where I'm going wrong. — Ryan Oneal
When I run. I get " Run time error 1004" "We can't paste because the copy area and the paste area aren't the same size". — Ryan Oneal

Marek Stejskal Marek Stejskal · Accepted Answer · 2015-02-03T20:35:34

This piece of code

Set NextRow = ws.Cells(Cells.Rows.Count).End(xlUp).Offset(1)

selects the very last column of your worksheet, that's why you cannot paste anything that's more than one column wide. You need to modify the logic behind finding the NextRow.

EDIT:

This minor change will do it:

Set NextRow = ws.Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1)

Apparently, the default ColumnIndex parameter for Cells() is not 1, you need to explicitly set it.

Excel - Copying pivot tables on multiple sheets and pasting as values (retaining formatting)

1 Answers