Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

vba pivot tables

ResolvedVersion 2010

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

RE: VBA Pivot Tables

Thank you very much for that - it worked!

 

Training courses

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Access tip:

Hiding rows and columns

To hide a column or row in Access, you need to minimise the column or row. This can be done by placing your mouse on the right edge of a column, wait for the mouse to change to a double aroow and then drag to minimise the column

View all Access hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.12 secs.