VBA Multiple Chart Filters

Question

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!

are the charts pivotcharts?

Scott Holtzman
– Scott Holtzman

2017-10-17 16:53:05 +00:00
Commented Oct 17, 2017 at 16:53 — Scott Holtzman
– Scott Holtzman, Commented Oct 17, 2017 at 16:53

Scott Holtzman · Accepted Answer · 2017-10-17 17:18:32Z

2

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

answered Oct 17, 2017 at 17:18

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

T Dale Over a year ago

Thanks, Scott! Definitely a lot left to learn, but I appreciate you helping me out with this project.

Collectives™ on Stack Overflow

VBA Multiple Chart Filters

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related