Pivot GetPivotData In VBA

[ajmplanner]

I have 2 worksheets
In Sheets ("Closed Trans") I have a Pivot Table: Col 1 is named CL Date and Col 2 is named Net Realized Gain

In Sheets("Summary") I have 2 columns: Col A and Col B. Col A contains a date
I want to extract the Net Realized gain from the pivot table in Sheets("Closed Trans") based on the date in COL A and insert it into Col B in Sheets("Summary")
There are nsummary number of dates in COl A

My code is

For i = 3 To nsummary
lkup = Format(Cells(i, 1).Value, "mm/dd/yy" )
cells(i,2).value=GETPIVOTDATA("Net Realized Profit",'Closed Trans'!$A$1,"Cl Date","), lkup)

Next i

The compiler will not accept my formula for rg. I tried including WorksheetFunction.GetPivotData, and also a specific date for lkup with same result for both

However, if I type =GETPIVOTDATA("Net Realized Profit",'Closed Trans'!$A$1,"Cl Date","), "03/15/21") directly into a cell in Sheets("Summary"), I get a correct result
What am I missing here? Thanks in advance for any replies.

 

[ajmplanner]

I have 2 worksheets
In Sheets ("Closed Trans") I have a Pivot Table: Col 1 is named CL Date and Col 2 is named Net Realized Gain

In Sheets("Summary") I have 2 columns: Col A and Col B. Col A contains a date
I want to extract the Net Realized gain from the pivot table in Sheets("Closed Trans") based on the date in COL A and insert it into Col B in Sheets("Summary")
There are nsummary number of dates in COl A

My code is

For i = 3 To nsummary
lkup = Format(Cells(i, 1).Value, "mm/dd/yy" )
cells(i,2).value=GETPIVOTDATA("Net Realized Profit",'Closed Trans'!$A$1,"Cl Date","), lkup)

Next i

The compiler will not accept my formula for rg. I tried including WorksheetFunction.GetPivotData, and also a specific date for lkup with same result for both

However, if I type =GETPIVOTDATA("Net Realized Profit",'Closed Trans'!$A$1,"Cl Date","), "03/15/21") directly into a cell in Sheets("Summary"), I get a correct result
What am I missing here? Thanks in advance for any replies.

Shouldhave said formula cor cells(i,2).value