使用VBA更改Pivot表数据源

I am trying to automatically try to change the "Data Source" of a pivot table in excel. I have tried most if not all of the solutions available on Google/Stack Overflow. However I keep encountering the following error.

我正在尝试自动尝试更改excel中的数据透视表的“数据源”。我已经尝试了谷歌/Stack Overflow上所有可用的解决方案。但是我一直遇到以下错误。

Run-time error '5':
Invalid procedure call or argument

Here is the code that I'm running that fails.

这是我正在运行的失败代码。

Sub UpdateResourcePivots()
Dim pivotDataSheet As Worksheet
Dim dataSheet As Worksheet
Dim pivotLocation As String
Dim pivotDesignation As String
Dim startPoint As Range
Dim endPoint As Range
Dim dataRange As Range
Dim newRange As String
Dim lastRow As Integer

Dim newPivotCache As PivotCache

Set pivotDataSheet = ThisWorkbook.Worksheets("PivotData")
Set dataSheet = ThisWorkbook.Worksheets("Team Information")

pivotLocation = "pvtResourceLocation"
pivotDesignation = "pvtResourceDesignation" 'Dont worry about this one for now

Set startPoint = dataSheet.Range("A5")
lastRow = dataSheet.Range("A" & dataSheet.Rows.count).End(xlUp).row
Set endPoint = dataSheet.Range("O" & lastRow)
Set dataRange = dataSheet.Range(startPoint, endPoint)

newRange = "'" & dataSheet.name & "'!" & dataRange.Address(RowAbsolute:=True, ColumnAbsolute:=True, ReferenceStyle:=xlR1C1, External:=True)
' newRange = "'" & dataSheet.name & "'!" & dataRange.Address(ReferenceStyle:=xlR1C1) ' Tried this as well

Set newPivotCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=newRange, Version:=xlPivotTableVersion10)

With pivotDataSheet
    .PivotTables(pivotLocation).ChangePivotCache newPivotCache 'Error thrown here.

End With
End Sub

The error is thrown at the following line:

错误抛出如下一行:

.PivotTables(pivotLocation).ChangePivotCache newPivotCache

Some other info

其他一些信息

I am using MS Office 2010 (licensed). I have one source of data (a small table with 20 rows and 8 columns; the rows will grow hence the macro). Created two Pivot Tables on another sheet. On a third sheet I'm using Slicers to filter the data (Dashboard).

我正在使用MS Office 2010(许可)。我有一个数据源(一个包含20行和8列的小表;行会因此而增大)。在另一个表上创建了两个数据透视表。在第三张表中，我使用切片器来过滤数据(仪表板)。

1 个解决方案

#1

Change the code so that you create the cache as you assign it to the pivot table:

更改代码，以便在将缓存分配给pivot表时创建缓存:

With pivotDataSheet
    .PivotTables(pivotLocation).ChangePivotCache ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=dataRange.Address(Referencestyle:=xlR1C1), Version:=xlPivotTableVersion10)