开发者

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 who开发者_运维知识库le new set.) How can I reference the old pivot tables?

I can use VBA to do this if necessary.


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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜