Excel: Formula to reference an entire pivot table?

Question

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

I can use VBA to do this if necessary.

Answer 1

This sub will keep the pivot tables 'live.' You could PasteValues over them if you don't want that.

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