Clever Geek Handbook

Formula for referencing the entire pivot table?

I have a bunch of sheets with detailed datasets and pivot tables. On a pivot sheet, I only want to display pivot tables. (Of course, I'd rather stay DRY and not create a completely new set.) How can I reference the old pivot tables?

I can use VBA for this if needed.

+2


source to share


1 answer


This subtitle will contain live pivot tables. You can PasteValues ​​above them if you don't want to.



Sub SummarizePivotTables()
    Dim wb As Workbook, ws As Worksheet, ss As Worksheet, pt As PivotTable
    Dim pasteRow As Long
    Const rowsBetween As Long = 1

    Set wb = ThisWorkbook
    Set ss = wb.Worksheets("Summary")
    pasteRow = 1 'first table row'

    For Each ws In wb.Worksheets
        For Each pt In ws.PivotTables
            'change this to TableRange1 if you do not want the page field included'
            With pt.TableRange2
                .Copy ss.Range("A" & pasteRow)
                pasteRow = pasteRow + .Rows.Count + rowsBetween
            End With
        Next pt
    Next ws
End Sub
+1


source






More articles:


All Articles