CODEWITHSUNDEEP
vbaexcel
T Dale
T Dale

Reputation: 11

VBA Multiple Chart Filters

Pretty new to VBA. I have a workbook that has about 8 charts on a tab and there are a lot of tabs. Each chart needs to be filtered for the same values, and I'm struggling to get my macro to work. Here's what I have:

Sub ChartFilter()
'
' 
'

'
For Each Chart In ActiveWorkbook.Charts
    ActiveChart.Legend.Select
    With ActiveChart.PivotLayout.PivotTable.PivotFields("Category1")
        .PivotItems("Value1").Visible = True
        .PivotItems("Value2").Visible = True
        .PivotItems("Value3").Visible = True
        .PivotItems("Value4").Visible = True
        .PivotItems("Value5").Visible = True
        .PivotItems("Value6").Visible = True
        .PivotItems("Value7").Visible = True
    End With
    Next Chart
End Sub

Do you know where I'm going wrong?

Thanks!

Upvotes: 1

Views: 369

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27259

There are many places in the code where the syntax is wrong. I have re-written and tested below. This assumes your charts are PivotCharts

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

    Dim pt As PivotTable

    For Each pt In ws.PivotTables

        Dim pi As PivotItem

        For Each pi In pt.PivotFields("Category1").PivotItems

            Select Case pi.Name

                Case Is = "Value1", "Value2", "Value3", "Value4", "Value5", "Value6", "Value7"
                    pi.Visible = True
                Case Else
                    pi.Visible = False

            End Select

        Next

    Next

Next

Upvotes: 2

Related Questions