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

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" wrote:

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

THANKS!!

"Barb Reinhardt" wrote:

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" wrote:

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