时间戳

首页 > 解决方案 > 创建 VBA 以更改多个数据透视表中的数据源时出错

vba - 创建 VBA 以更改多个数据透视表中的数据源时出错

问题描述

我正在尝试创建一个 VBA 来更改同一数据文件和工作表中多个数据透视表中的数据源。数据源位于另一个工作簿中，并且每周都会使用新数据进行更新。

“FY 18.xlsb” - 包含数据的工作簿

“数据” - “FY 18.xlsb”中的工作表，其中包含数据

“数据透视表” - 活动工作簿中的工作表，其中可以找到应该更新的数据透视表。

我希望 VBA 在数据工作表中找到终点，或者有一个可以选择数据范围的弹出窗口。

在“pt.ChangePivotCache pc”的代码上运行调试时停止

任何人有任何想法可能是错的？或者任何想法如何更新代码？

Sub AdjustPivotDataRange()
Dim pt As PivotTable, pc As PivotCache
Dim dataSheet As Worksheet, ws As Worksheet, wks As Worksheet
Dim StartPoint As Range, dataSource As Range, NewRange As String

' Datasource workbook and worksheet
Set wkb = Workbooks("FY 18.xlsb")
Set wks = wkb.Worksheets("Data")

' Worksheet
Set ws = ActiveWorkbook.Worksheets("Pivot Table")

' Dynamically retrieve range address of data
Set StartPoint = wks.Range("A1")
Set dataSource = wks.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))
NewRange = wks.Name & "!" & dataSource.Address(ReferenceStyle:=xlR1C1)

' Create new PivotCache
Set pc = ThisWorkbook.PivotCaches.Create( _
           SourceType:=xlDatabase, _
           SourceData:=NewRange)

' Loop through all tables in worksheet
For Each pt In ws.PivotTables

        ' update pivot source and refresh
        pt.ChangePivotCache pc
        pt.RefreshTable

Next pt 
End Sub

编辑：做我想要的新代码！

Sub AdjustPivotDataRange()
Dim pt As PivotTable, pc As PivotCache
Dim dataSheet As Worksheet, ws As Worksheet, wks As Worksheet
Dim StartPoint As Range, dataSource As Range, NewRange As String

' Datasource workbook and worksheet
Set wkb = Workbooks("Nordics SQI - FY 19.xlsb")
Set wks = wkb.Worksheets("Data")

' Worksheet
Set ws = ActiveWorkbook.Worksheets("Pivot Table")

' Dynamically retrieve range address of data
 Set dataSource = wks.Range("A1").CurrentRegion

' Loop through all tables in worksheet
For Each pt In ws.PivotTables

        ' update pivot source and refresh
        pt.ChangePivotCache ThisWorkbook.PivotCaches.Create( _
           SourceType:=xlDatabase, _
           SourceData:=dataSource)
        pt.RefreshTable

Next pt

End Sub

标签： vbaexcelpivot-table

解决方案

第一个想法- PivotCache 数据源应该只是一个连续的范围，因此请尝试以下操作：

'Set StartPoint = wks.Range("A1") 'not required
Set dataSource = wks.Range("A1").currentregion
'NewRange = wks.Name & "!" & dataSource.Address(ReferenceStyle:=xlR1C1) 'not required

' Create new PivotCache
Set pc = ThisWorkbook.PivotCaches.Create( _
           SourceType:=xlDatabase, _
           SourceData:=dataSource) '!!

编辑参考已编辑的问题：

我不确定在 lop 中为每个单独的数据透视表创建新的 PivotCache 是否有效。尝试将此解决方案结合我的第一个答案和您提出的解决方案：

未测试

Dim newPC as PivotCache
Set newPC = ThisWorkbook.PivotCaches.Create( _
           SourceType:=xlDatabase, _
           SourceData:=dataSource)

For Each pt In ws.PivotTables

        ' update pivot source and refresh
        pt.ChangePivotCache newPC
        pt.RefreshTable

Next pt

推荐阅读