Excel 2013 vba - 刷新数据透视表 - 循环

I have some vb code that will refresh my pivot tables for me, is there a way to loop this so I can refresh pivot tables 1 to 20 say? Rather than listing 20 lines of code below Eg

我有一些vb代码会为我刷新我的数据透视表,有没有办法循环这个所以我可以刷新数据透视表1到20说?而不是在Eg下面列出20行代码

ActiveWorkbook.PivotTables(1).PivotCache.Refresh
ActiveWorkbook.PivotTables(2).PivotCache.Refresh
ActiveWorkbook.PivotTables(3).PivotCache.Refresh

Etc..

Many thanks in advance!

提前谢谢了!

3 个解决方案

#1

As an example: If you want to refresh all pivot caches in your workbook, you can do something like this:

例如:如果要刷新工作簿中的所有数据透视缓存,可以执行以下操作:

Sub RefreshPivotCache()
    Dim ws As Worksheet
    Dim PT As PivotTable

    For Each ws In ActiveWorkbook.Worksheets '<~~ Loop all worksheets in workbook
        For Each PT In ws.PivotTables        '<~~ Loop all pivot tables in worksheet
            PT.PivotCache.Refresh
        Next PT
    Next ws
End Sub

#2

You can use this, this will also go through each worksheet in the workbook, and each pivot table in each sheet and refresh & update the table.

您可以使用它,这也将遍历工作簿中的每个工作表,以及每个工作表中的每个数据透视表并刷新和更新表。

Sub RefreshPivotData()
Dim pvotCht As PivotTable, Sheet As Worksheet
    For Each Sheet In ThisWorkbook.Worksheets
        For Each pvotTbl In Sheet.PivotTables
            pvotTbl.RefreshTable
            pvotTbl.Update
        Next
    Next
ThisWorkbook.Save
End Sub

#3

try this:

Option Explicit

Sub RefresfPivotTables()

Sheets("YOUR_SHEET").Select

Dim i As Integer

For i = 1 To 20

    ActiveSheet.PivotTables("PivotTable" & i).PivotCache.Refresh

Next i

End Sub

#1