Pivot Restrict Macro Only Works for 1 table

[Visakha]

Hi,

I got a macro to restrict pivot table functions from Debra. It seems to work
well but only for 1 table out of 4 that I have on a worksheet. All the
tables work from the same data source. Basically I only want end-users to be
able to refresh and use drop downs. Also strange is that I may try to run
the macro on the 2nd one but the macro is applied to the 3rd. Please help
Debra or anyone else! I am a beginner at macros. Thanks!

Sub RestrictPivotTable()
Dim pf As PivotField
With ActiveSheet.PivotTables(1)
.EnableWizard = False
.EnableDrilldown = False
.EnableFieldList = False 'Excel 2002+
.EnableFieldDialog = False
.PivotCache.EnableRefresh = True
For Each pf In .PivotFields
With pf
.DragToPage = False
.DragToRow = False
.DragToColumn = False
.DragToData = False
.DragToHide = False
End With
Next pf
End With

End Sub

 

[Barb Reinhardt]

Try this

Option Explicit

Sub RestrictPivotTable()
Dim pf As Excel.PivotField
Dim myPivotTable As Excel.PivotTable

For Each myPivotTable In ActiveSheet.PivotTables
With myPivotTable
'With ActiveSheet.PivotTables(1)
.EnableWizard = False
.EnableDrilldown = False
.EnableFieldList = False 'Excel 2002+
.EnableFieldDialog = False
.PivotCache.EnableRefresh = True
For Each pf In .PivotFields
With pf
.DragToPage = False
.DragToRow = False
.DragToColumn = False
.DragToData = False
.DragToHide = False
End With
Next pf
End With
Next myPivotTable

End Sub

 

[Visakha]

THANKS!!

Try this

Option Explicit

Sub RestrictPivotTable()
Dim pf As Excel.PivotField
Dim myPivotTable As Excel.PivotTable

For Each myPivotTable In ActiveSheet.PivotTables
With myPivotTable
'With ActiveSheet.PivotTables(1)
.EnableWizard = False
.EnableDrilldown = False
.EnableFieldList = False 'Excel 2002+
.EnableFieldDialog = False
.PivotCache.EnableRefresh = True
For Each pf In .PivotFields
With pf
.DragToPage = False
.DragToRow = False
.DragToColumn = False
.DragToData = False
.DragToHide = False
End With
Next pf
End With
Next myPivotTable

End Sub