VBA to copy and paste Grand Total from Pivot Table

Question

I am hoping to create a macro to copy the value of a grand total from a pivot table on another workbook and paste it to ThisWorkbook. Here is what I have so far:

Notes:

• I do not want to just copy the bottom right cell value of the Pivot Table as that has been a solution to other questions asked before. I will eventually need to copy from multiple Grand Totals that are not in the bottom right corner.
• Currently getting Run-time error 424. Object required

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

You are declaring your pivot table as one variable, and then trying to reference it as a different one.

Your declaration: Dim pvt1 as PivotTable

Your reference: WsDestination.Range("B4") = pvt.GetPivotData("Sum of Dollars","Type","Good","Year Month","2022 March")

Change pvt to pvt1.