VBA从Pivot表中复制并粘贴Grand总数

Question

我希望创建一个宏来从另一个工作簿上的枢轴表中复制一个总数的价值，然后将其粘贴到本工作簿中。这是我到目前为止所拥有的：

注：

• 我不想仅复制枢轴表的右下方单元格值，因为这是对以前提出的其他问题的解决方案。我最终将需要从不在右下角的多个大总数中复制。
• 当前遇到运行时错误424。需要对象

Dim wbSourceData As Workbook
Dim wbDestination As Workbook
Dim wsSourceData As Worksheet
Dim wsDestination As Worksheet
Dim wsMacros As Worksheet
Dim pvt1 as PivotTable
Dim strFName as String

Set wbDestination = ThisWorkbook
Set wsDestination = wbDestionation.Sheets("Paste Total Here") 'Tab I want data pasted to
Set wsMacros = wbDestination.Sheets("Macros") 'I have a worksheet with dynamic references for my code as the location of the Source Data changes every month

strFName = wsMacros.Range("C107").Value 'The file path to open file

Set wbSourceData = Workbooks.Open(strFName, , ReadOnly:=True)
Set wsSourceData = wbSourceData.Worksheets("Copy From Here") 'Tab I copy data from

With wsSourceData
Set pvt1 = .PivotTables("PivotTable1")
WsDestination.Range("B4") = pvt.GetPivotData("Sum of Dollars","Type","Good","Year Month","2022 March") 'I want to copy Grand Total "Dollars" of the columns "Good" and "2022 March" to be pasted into cell B4 of the worksheet destination sheet of This Workbook
End With

wbSourceData.Close SaveChanges:=False

End Sub

Answer 1

您将枢轴表声明为一个变量，然后尝试将其称为另一个变量。

您的声明：
DIM PVT1作为枢纽

您的参考：
wsdestination.range（“ b4”）= Pvt.getPivotData（“美元总和”，“类型”，“良好”，“年度”，“ 2022年3月”）

更改pvt<代码> pvt1。