更新透视数据范围字符串或范围

Question

我正在处理一个我拼凑在一起的代码，但我发现在找到"使用"范围后(由于可靠性，我尽量不使用.usedrange(是SourceData:=期待一个字符串(我认为(。

有没有办法传递从Data表到数据透视表数据范围的范围？我尝试附加RealUsedRange.Address，但这也不是运气。

Sub UpdatePivotRange()
Dim Rng1        As Range
Dim oWB         As Workbook
Dim oWS         As Worksheet
Dim DataSheet   As Worksheet
Dim oPT         As PivotTable
Set oWB = ThisWorkbook
Set DataSheet = oWB.Sheets("Data")
Set Rng1 = RealUsedRange
If Rng1 Is Nothing Then
MsgBox "There is no used range, the worksheet is empty."
Else
For Each oWS In oWB.Worksheets
For Each oPT In oWS.PivotTables
'ERRROR BEGINS HERE #####
oPT.ChangePivotCache _
oWB.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Rng1)
'ERROR ENDS HERE #####
Next oPT
Next oWS
End If
End Sub
Public Function RealUsedRange() As Range
Dim FirstRow        As Long
Dim LastRow         As Long
Dim FirstColumn     As Integer
Dim LastColumn      As Integer
Dim DataSheet       As Worksheet
Dim oWB             As Workbook
On Error Resume Next
Set oWB = ThisWorkbook
Set DataSheet = oWB.Sheets("Data")
With DataSheet
FirstRow = DataSheet.Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
FirstColumn = DataSheet.Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column
LastRow = DataSheet.Cells(.Rows.Count, "A").End(xlUp).Row
LastColumn = DataSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set RealUsedRange = Range(Cells(FirstRow, FirstColumn), Cells(LastRow, LastColumn))
End With
MsgBox "The range is" & RealUsedRange.Address
On Error GoTo 0
End Function

Answer 1

我能够通过添加以下内容来修改错误的行：

oPT.ChangePivotCache _
oWB.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Data!" & Rng1.Address(ReferenceStyle:=xlR1C1))

希望这对未来的任何人有所帮助。