Tarla has attended:
Excel VBA Intro Intermediate course
Restricting multiple pivot tables
Hi,
Code below works when you run it within a worksheet that has a pivot table but I need to run it from a separate worksheet and want it to update all the pivot tables within the workbook.
Sub RestrictPivotTable()
Dim pf As PivotField
With ActiveSheet.PivotTables(1)
.EnableWizard = False
.EnableDrilldown = False
.EnableFieldList = False
.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
Please help.
RE: 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
