Andrzej has attended:
Excel VBA Intro Intermediate course
VBA Pivot Tables
Hi there,
I am tring to create a macro that gues through individual tabs on a workbook, recognises if there is a pivot table there and restrict a single field. I have managed to create most of it (see below), but I am struggling with "recognise" bit - the macro should perform the operation only on the tabs of the report where there are pivot tables. If statement "if.activesheet.pivottables <> """ doesn't work for boolean. Has anyone got a solution for it?
Sub RestrictSingleField()
Dim pt As PivotTable
Dim pf As PivotField
Application.ScreenUpdating = False
For S = 8 To Worksheets.Count
Sheets(S).Select
Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PivotFields("RMCC AD")
ActiveSheet.PivotTables("PivotTable1").PivotFields("RMCC AD").CurrentPage = "Name"
With pf
.EnableItemSelection = False
.DragToHide = False
.DragToPage = False
.DragToRow = False
.DragToColumn = False
.DragToData = False
End With
Next S
End Sub
RE: VBA Pivot Tables
Hi Andrzej
Thanks for getting in touch.
Approach this problem from a different perspective: let a FOR EACH loop figure out where the PivotTables are. Another benefit of this approach will also deal with multiple PivotTables on the same sheet.
Dim Wksh As Worksheet ' the current worksheet from the colection of workbooks
Dim Pvt As PivotTable ' the current pivot table from the current worksheet
' Process all sheets
For Each Wksh In ActiveWorkbook.Worksheets
For Each Pvt In Wksh.PivotTables
' Perform your function
Next Pvt
Next Wksh
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
