Restricting multiple pivot tables

Hi Tarla

Thanks for getting in touch. You need to expand your code with another loop that checks all sheets in the workbook for PivotTables.

The following code refreshes all PivotTables in the current workbook.

Sub RefreshAllPivotTables()

Dim PT As PivotTable
Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets

For Each PT In WS.PivotTables
PT.RefreshTable
Next PT

Next WS

End Sub

Extending this idea into your code we get:

Sub RestrictPivotTables()

Dim PT As PivotTable
Dim WS As Worksheet
Dim PF As PivotField

For Each WS In ThisWorkbook.Worksheets

For Each PT In WS.PivotTables

PT.EnableWizard = False
PT.EnableDrilldown = False
PT.EnableFieldList = False
PT.EnableFieldDialog = False
PT.PivotCache.EnableRefresh = True

For Each PF In PT.PivotFields

With PF
.DragToPage = False
.DragToRow = False
.DragToColumn = False
.DragToData = False
.DragToHide = False
End With

Next PF

Next PT

Next WS

End Sub

I hope this helps.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector