CODEWITHSUNDEEP
excelpivot-tablevba
Nick Heiner
Nick Heiner

Reputation: 122432

Formula to reference an entire pivot table?

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.

Upvotes: 3

Views: 1670

Answers (2)

Kashyap
Kashyap

Reputation: 17411

Pivot tables are named "Excel tables" in your workbook. So you should be able to do this without VB as I described in this answer.

tl;dr;

  • Data -> Get External Data -> Existing Connections
  • Goto "Tables" tab, select your table
  • Insert as "Table"

Upvotes: 0

Ryan Shannon
Ryan Shannon

Reputation: 710

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

Upvotes: 1

Related Questions