VBA - Uncheck dynamic range in Pivot Field

[Goose306]

Hi all,

Got a question, should be relatively straightforward but I'm struggling this morning. I have a macro that runs through reports that are generated by SAS and automatically manipulates them based on parameters set by a user.

• There are new variables that have come up that need to be automatically excluded in most situations. The issue is, this variable goes under several names that is regularly changing.
• To make it easier to maintain, I've decided to make a list of these variables in the master file where users set parameters so these can be accounted for.

I haven't previously worked with looping through a dynamic range to set pivot items/remove pivot items, so I'm unsure on how to best proceed. Here's a snippet of current code that is currently not working with a nested To/For Loop:

  Public va2 As Worksheet     'Validation 2 tab
  Public ap As Worksheet      'Audit Parameters tab
  Public cel As Range         'Cell reference for loops
  Set ap = ThisWorkbook.Worksheets("Audit Parameters")  Set va2 = ThisWorkbook.Worksheets("VALIDATION2")
  Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables(1)
  Dim pi As PivotItem
  Dim varx As Range
    Set varx = va2.Range("M2", Range("M2").End(xlDown))

'Handle VarX
  If ap.Cells(9, 6) <> "" Then
    For Each pi In pt.PivotFields("Skill").PivotItems
      For Each cel In varx
        If pi.Value = cel Then
          pi.Visible = True
        Else
          pi.Visible = False
        End If
      Next cel
    Next pi
  End If

Any help is appreciated, thanks!

 

[Goose306]

Bump. Any help is appreciated. I've also tried to change handling to running off a loop using an integer but no luck.

  'Handle VarX  If ap.Cells(9, 6) <> "" Then
    For i = 1 To varx.Count
      For Each pi In pt.PivotFields("Skill").PivotItems
        If pi.Value = varx.Cells(i).Value Then
          pi.Visible = False
        Else
          pi.Visible = True
        End If
      Next pi
    Next i
  End If