myGT1 = Evaluate("=GETPIVOTDATA(""NameOfField"",'" & mySheet.Name & "'!$A$3)")
MsgBox myGT1

etc....


HTH,
Bernie
MS Excel MVP


"circuit_breaker" wrote in message
...
Hi,

My VBA script needs to get 4 values from another sheet which contains
4 pivot tables. Specifically, I need to get the values next to "Grand
Total" in each pivot table. The sheet is auto-generated by another
script and no named cells are in use here.

I've been able to get to the last used cell but no futher:

Set mySheet = Workbooks(wbName).Sheets(wsName)
lastRow = mySheet.Cells.SpecialCells(xlCellTypeLastCell).Row

I could use the { Cells.Find(What:="Grand Total",... } function but
this would involve to activate the sheet containing the pivot tables
first and I'd like to avoid that.

Thanks for your help.