VBA Code for PivotTable

[cstimart]

I have been unable figure out VBA code to just extract the number/value, rather than filling a cell with the figure and then using the cell value to fill a variable.

Range("B22").Formula = "=GETPIVOTDATA(""Sum of Hours"",Pivot!$A$1,""Pay Period"",DATE(year($A$2),month($A$2),day($A$2)),""Office"",""Eastern"")"
number1 = Range("B22").value

Help, please?

 

[RoryA]

The PivotTable object has a GetPivotData method...

 

[cstimart]

So, I tried...

number1 = PivotTable2.GetPivotData("Sum of Reg Hours", "Pay Period", Range("A2"), "Office", Range("A9"))

...and no dice, "Object Required".
Range("A2") is a Date field.
Range("A9") = "Eastern"

 

[RoryA]

Where/how did you declare and initialise your PivotTable2 variable?

 

[cstimart]

Where/how did you declare and initialise your PivotTable2 variable?

OK, so I Declared PivotTable2 as PivotTable...

Now I get "Object variable or With block variable not set"

 

[RoryA]

And how did you assign a pivot table to it?

 

[cstimart]

Got it now!!

Thanks!