时间戳

首页 > 解决方案 > 使用 VBA 动态更改数据透视表源数据

excel - 使用 VBA 动态更改数据透视表源数据

问题描述

我正在尝试编写一个宏来动态调整数据透视表上的源数据。我已经阅读了本网站和其他论坛上的其他解决方案，但似乎无法让他们的“修复”发挥作用。我目前正在使用我在网上找到的一些代码的略微修改版本，这些代码的问题似乎比其他代码少。

我的数据透视表位于名为“Pivot”的工作表上，源数据位于名为“Source”的工作表上。

目前我的代码是：

Sub AdjustPivotSource()

Dim Data_sht As Worksheet
Dim Pivot_sht As Worksheet
Dim PivotName As String
Dim NewRange As String

'Set Variables Equal to Data Sheet and Pivot Sheet
  Set Data_sht = Worksheets("Source")
  Set Pivot_sht = Worksheets("Pivot")

'Enter in Pivot Table Name
  PivotName = "My_Pivot"

Dim LastRow As Long
LastRow = Worksheets("Source").Range("A" & Rows.count).End(xlUp).Row

 NewRange = ActiveSheet.Name & "!" & Range("$A$1:$DU$" & LastRow).Address(ReferenceStyle:=xlR1C1)


'Change Pivot Table Data Source
  Pivot_sht.PivotTables(PivotName).ChangePivotCache _
    ThisWorkbook.PivotCaches.Create( _
      SourceType:=xlDatabase, _
      SourceData:=NewRange, Version:=xlPivotVersion15)

 'Refresh Pivot
   Pivot_sht.PivotTables(PivotName).RefreshTable
  End Sub

"Run-time error '5': Invalid procedure call or argument"这部分代码突出显示时出现错误：

 Pivot_sht.PivotTables(PivotName).ChangePivotCache _
ThisWorkbook.PivotCaches.Create( _
  SourceType:=xlDatabase, _
  SourceData:=NewRange, Version:=xlPivotVersion15)

我NewRange似乎准确地抓住了我想要的范围。我已经验证源数据没有空白标题。我已经仔细检查了数据透视表名称和工作表名称是否正确。

我想知道代码错误部分中的“ThisWorkbook”是否有问题？我最初使用的是：

 Set pivotDataSheet = ThisWorkbook.Worksheets("Source")

代替：

 Set pivotDataSheet = Worksheets("Source")

这导致了以下错误：

Run-time error '9': Subscript out of range"

我删除了“ThisWorkbook”并解决了该错误，但我没有从当前给我错误的代码部分中删除“ThisWorkbook”。

“ThisWorkbook”给出了错误，因为我是从 PERSONAL 而不是在文件本身中运行宏。仍然有主要问题。

标签： excelvbapivot-table

解决方案

虽然不是对我遇到问题的代码的真正修复，但下面的代码可以解决我的问题。我将源数据设为命名表，然后将数据透视表的源设置为表。这使我不必每次都重置数据透视缓存/数据源。

Worksheets("Source").ListObjects.Add(xlSrcRange, Range(Cells(1, "A").End(xlDown), Cells(1, "A").End(xlToRight)), , xlYes).Name = "Source_Table"

推荐阅读