Pivot Restrict Macro Only Works for 1 table
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
|