VBA数据透视表范围更改 [英] VBA Pivot Table Range change

问题描述

我有一个数据透视表，它的范围和数据总是改变我有以下数据：

I have a PivotTable that it's range and data always changes I have the below data:

Range("A3").Select
Selection.CurrentRegion.Select
DataArea = "Sheet21!R1C1:R" & Selection.Rows.Count & "C" & Selection.COLUMNS.Count
ActiveSheet.PivotTables("PivotTable7").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:=DataArea, _
        Version:=xlPivotTableVersion15)

我收到错误

无法获取Worksheet类的数据透视表属性

Unable to get the PivotTables property of the Worksheet class

推荐答案

代码将修改Sheet1中的PivotTable7（修改为您的工作表名称， 数据透视表），来自工作表（Sheet21）的 SourceData 范围（A3 ）.CurrentRegion 。

The code will modify "PivotTable7" in "Sheet1" (modify to your sheet's name where you have your PivotTable), with the SourceData from Worksheets("Sheet21").Range("A3").CurrentRegion.

Option Explicit

Sub ChangePivotTableCache()

Dim PvtTbl  As PivotTable
Dim DataArea As String

' fully qualify your range by adding the worksheet object
With Worksheets("Sheet21").Range("A3").CurrentRegion
    DataArea = "Sheet21!R1C1:R" & .Rows.Count & "C" & .Columns.Count
End With

On Error Resume Next
Set PvtTbl = Worksheets("Sheet1").PivotTables("PivotTable7") '<-- modify "Sheet1" to your sheet's name

On Error GoTo 0
If Not PvtTbl Is Nothing Then ' <-- If pivot table exist
    PvtTbl.ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=DataArea)
Else ' <-- If pivot table doesn't exist
    MsgBox "Pivot Table doesn't exist in worksheet!"
End If

End Sub

注意：要获取 SourceData 的 String ，您还可以使用：

Note: to get the String of the SourceData you could also use:

DataArea = "Sheet21!" & Worksheets("Sheet21").Range("A3").CurrentRegion.Address(True, True, xlR1C1)

这篇关于VBA数据透视表范围更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！