使用VBA更新Excel中的多个数据透视表

dxxyhpgq 于 2023-01-18 发布在其他

关注(0)|答案(2)|浏览(292)

我有3个工作表，其中第一个工作表包含数据，第二个是数据透视表，而第三个工作表是从数据透视表生成的数据透视图。我想创建一个VBA更新按钮，一旦数据源发生变化，它将更新数据透视表和数据透视图。
以下是我目前所做的工作：

Sub Update_Pivot()

    Dim pt As PivotTable
    Dim ws As Worksheet
    Dim lr As Long
    Dim rng As Range
    
    lr = ActiveWorkbook.Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
    Set rng = ActiveWorkbook.Sheets("Data").Range("A3:AR" & lr)
    
    For Each ws In ActiveWorkbook.Worksheets
    
    For Each pt In ws.PivotTables
             pt.ChangePivotCache ActiveWorkbook. _
                PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rng)
    Next pt
    Next ws
      
End Sub

然而，我收到一个错误，其中错误是在pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rng)中，说运行时错误“5”。
我的编码正确吗？我如何解决这个错误？

excel

来源：https://stackoverflow.com/questions/75127234/update-multiple-pivottables-in-excel-using-vba

2条答案

按热度按时间

dldeef671#

如果你在工作表中循环，在每个工作表上，在枢轴中循环，你应该很接近...
将下面的代码添加到...之后

For Each pt

看能不能帮上忙...

Application.ScreenUpdating = False 'This line disable the on screen update for better performance, the blink you see, you could delete both lanes but it will run slower
Dim myChart As ChartObject
For Each myChart In ActiveSheet.ChartObjects
    myChart.Chart.Refresh
Next myChart
Application.ScreenUpdating = True'This line reenable the on screen update for better performance, the blink you see, you could delete both lanes but it will run slower

赞(0）回复(0）举报 2023-01-18

camsedfj2#

这应该行得通：

Sub Update()

For Each Sheet In ThisWorkbook.Worksheets
            For Each Pivot In Sheet.PivotTables
                Pivot.RefreshTable
                Pivot.Update
            Next
            Dim myChart As ChartObject
            For Each myChart In ActiveSheet.ChartObjects
                myChart.Chart.Refresh
            Next myChart
Next
For Each Chart In ThisWorkbook.Charts ' if chart is not part of a sheet, but on its own 
            Chart.Refresh
        Next
End Sub

赞(0）回复(0）举报 2023-01-18

我来回答

使用VBA更新Excel中的多个数据透视表

2条答案

相关问题

热门标签

最新问答