Pivot table & VBA help

[Alpacino]

I have this macro:

Sub PivotEx1()
Range("H3").Select
ActiveCell.FormulaR1C1 = "=GETPIVOTDATA(""Sum of Contracted Hrs"",R3C1,""Dept"",""Warehouse"")"

Range("I3").Select
ActiveCell.FormulaR1C1 = "=GETPIVOTDATA(""Sum of Contracted Hrs"",R3C1,""Dept"",""Office"")"
Range("A1").Select

End Sub

What I would like to do is able to change the area "Warehouse" with a value from cell ("A2") a data validation full of different depts. Can someone help me to modify this please.

 

[Marcelo Branco]

Hi,

Maybe this

ActiveCell.FormulaR1C1 = "=GETPIVOTDATA(""Sum of Contracted Hrs"",R3C1,""Dept""," & """" & Range("A2") & """" & ")"

HTH

M.

 

[Alpacino]

Thanks works a treat.

 

[Marcelo Branco]

Thanks works a treat.

You are welcome!

Tks for providing feedback

M.