VBA and Pivot Table

Khalil · October 19th, 2012, 04:23 AM

Hi,

I have a pivot table that contains several regions and each region has its own worksheet. The Pivot table is sitting in one worksheet and I would like to move it to any worksheet that I want when activating it. Every time I do this, I get the following run time error 1004:

"Unable to get the PivotTables property of the worksheet class".
Can anyone please explain how to get rid of this silly message.

Code:

My code is:
 
Sub PivotMove()
 If Worksheets("All") Is ActiveSheet Then
 ActiveSheet.PivotTables("PivotTable1").Location = "All!$I$65"
 With ActiveSheet.PivotTables("PivotTable1").PivotFields("Region")
 .PivotItems("All").Visible = True
 End With
 
End Sub

Any help would be appreciated.

Thanks