使用vba更改数据透视源将不起作用

荷兰人Arjo

我一直在迷惑vba更改多个枢轴的数据源。我一直在阅读许多建议，但到目前为止，这些建议都没有对我有帮助。我不明白自己在做什么错，所以最终我在这里提出了自己的问题。

这是我要实现的目标：我有多个具有枢轴的文件，由于插入了列并且枢轴数据源已更改，因此必须更改枢轴。我想将其更改为正确的范围。我创建了以下代码：

Sub change_pivotsource()
Dim pt As PivotTable
Dim pts As PivotTables
Dim pt_source As String
Dim ws As Worksheet
Dim wb As Workbook

Set wb = ActiveWorkbook

workbookname = ActiveWorkbook.Name
sheetname = Left(workbookname, Len(workbookname) - 5)
pt_source = "'" & sheetname & "'!$A:$T"


For Each ws In wb.Worksheets
    For Each PivotTable In ws.PivotTables
      pt.SourceData = pt_source
    Next PivotTable
Next ws


End Sub

我正在使用工作簿名称来包含字符串，这是最终数据源位置。

如何将pt_source作为工作簿中每个枢轴的枢轴源？很多时候，我收到“未设置对象或块变量”错误

编辑1：

此代码在执行期间给我一个错误7：内存不足：然后突出显示“ pt_source =“-row。

Sub change_pivotsource()
Dim ws As Worksheet
Dim wb As Workbook
Dim pt As PivotTable
Dim sheetname As String
Dim pt_source As String
Dim workbookname As String

workbookname = ThisWorkbook.Name
sheetname = Left(workbookname, Len(workbookname) - 5)
pt_source = Sheets(sheetname).Range("$A:$T")

For Each ws In ThisWorkbook.Worksheets
      For Each pt In ws.PivotTables
        pt.ChangePivotCache _
        wb.PivotCaches.Create(SourceType:=xlDatabase, _
              SourceData:=pt_source)

      Next pt
Next ws
'workbook.save
End sub

这段代码解决了我的问题：

Sub UpdatePivotCaches()
' Updates each pivot cache in the current workbook.
Dim pt As PivotTable        ' Used to loop over each pivot table.
Dim ws As Worksheet         ' Used to loop over each work sheet.
Dim sheetname As String
Dim pt_source As String
Dim workbookname As String

workbookname = ThisWorkbook.Name
sheetname = Left(workbookname, Len(workbookname) - 5)


    ' Loop over each sheet.
    For Each ws In ActiveWorkbook.Sheets

        ' Loop over each pivot table on the sheet.
        For Each pt In ws.PivotTables

            ' Update the pivot cache.
            pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create(xlDatabase, "'" & sheetname & "'!$A:$T", xlPivotTableVersion14)
        Next
    Next
End Sub

解决方案的关键可能是version参数（请参阅此问题下方的注释）。多亏了目的地数据

大卫·拉什顿（David Rushton）

数据透视表从数据透视缓存中获取数据。这是我们需要更改的对象。此过程将替换连接到当前工作簿中每个数据透视表的缓存。

Sub UpdatePivotCaches()
' Updates each pivot cache in the current workbook.
Dim pt As PivotTable        ' Used to loop over each pivot table.
Dim ws As Worksheet         ' Used to loop over each work sheet.


    ' Loop over each sheet.
    For Each ws In ActiveWorkbook.Sheets

        ' Loop over each pivot table on the sheet.
        For Each pt In ws.PivotTables

            ' Update the pivot cache.
            pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create(xlDatabase, "'Sheet1'!C8:D12", xlPivotTableVersion14)
        Next
    Next
End Sub

我已经对新的数据源（...'Sheet1'!C8:D12...）进行了硬编码，但是您可以通过参数化范围地址来改进此过程。

如果您对VBA感到困惑，请尝试使用宏记录器。这会将您的手动操作转换为VBA。这是一个非常方便的工具。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。