CODEWITHSUNDEEP
vbaexcel
alex1stef2
alex1stef2

Reputation: 105

VBA code for running through pivot tables and changing filters/labels

Effectively I am trying to create some VBA code that will loop through a bunch of sheets with pivot tables, apply certain conditions to the pivot tables (i.e. change the fields depending on a variable/change the filter depending on a cell value, as I have tried to demonstrate in the code below). This doesn't even come close to running (I am a complete beginner trying to update legacy code), so I am looking for pointers as to how to use the variables defined for the results I want.

My current issue is that I can't seem to call the PivotTable to start changing fields etc.

Option Explicit
Dim MySheet As Worksheet
Dim MyPivot As PivotTable
Dim NewCat As String

Sub RefreshPivots()

 For Each MySheet In ActiveWorkbook.Worksheets
    For Each MyPivot In MySheet.PivotTables
        NewCat = Worksheets("WorkingSheet").Range("B44").Value
        MyPivot.RefreshTable

        With ActiveSheet
         .MyPivot.AddDataField
         .MyPivot.PivotFields ("GuidelinePercent"), "Count of GuidelinePercent", xlCount
        End With

    Next MyPivot
 Next MySheet

(A lot more will go in the loop, this is just the first issue I have encountered).

I am getting: Run-time error '438' Object doesn't support this property or method on line

With ActiveSheet.MyPivot.AddDataField

Any help would be great!

EDIT;

When trying 

With ActiveSheet
.PivotTables(MyPivot).AddDataField
.PivotTables(MyPivot).PivotFields ("GuidelinePercent"), "Count of GuidelinePercent", xlCount
End With

I get Run-time error'1004': Unable to get the Pivot Tables property of the Worksheet class on the line .MyPivot.AddDataField.

Upvotes: 0

Views: 440

Answers (1)

Shai Rado
Shai Rado

Reputation: 33682

You have a loop of For Each MySheet In ActiveWorkbook.Worksheets, so when you are using later With ActiveSheet it is not the same sheet as MySheet, infact you don't need to use ActiveSheet, you can use directly the PivotTable object you name MyPivot.

Try the code below:

 With MyPivot
     .AddDataField .PivotFields("GuidelinePercent"), "Count of GuidelinePercent", xlCount
 End With

Edit 1:

 Dim PvtFld As PivotField

 With MyPivot
     On Error Resume Next
     Set PvtFld = .PivotFields("GuidelinePercent")
     On Error GoTo 0
     If PvtFld Is Nothing Then
         MsgBox "Error in setting the PivotField"
     Else
         .AddDataField PvtFld, "Count of " & PvtFld.Name, xlCount
     End If
 End With

Upvotes: 3

Related Questions