VBA on Pivot Chart

Question

Hi,

I am very new in VBA on excel so my question might come off as confusing but I'll try to make the most sense of it.

I have two pivot charts (1 VBA programmed pivot chart, and one non-vba pivot chart) on 1 spreadsheet.  The details of the programming for the VBA pivot chart are:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

        ActiveSheet.AutoFilter.ApplyFilter

        Ctr = 1

        For Each Cell In Range("P41:P59").SpecialCells(xlCellTypeVisible)

        ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Points(Ctr).SecondaryPlot = Cell.Value

        Ctr = Ctr + 1

    Next Cell

End Sub

This is under Worksheet - PivotTableUpdate.

My VBA pivot chart worked successfully with the above code. However, the slicer for my non-vba pivot chart now has an error. When I click on the slicer button for the non-vba pivot chart, it says "Run-time error "91: Object Variable or With block variable not set." 

When i click debug, the error line was from "ActiveSheet.AutoFilter.ApplyFilter". If I remove that entire code, my non-vba pivot chart slicer works perfectly but my VBA pivot chart doesn't.

Is there a way I can vba program JUST the vba pivot chart and leave the non-vba pivot chart alone?

Again, sorry if this was confusing but I would REALLY APPRECIATE ANY HELP I CAN GET HERE.

Thanks.

Marcus

Answer 1

You may create a copy and see if the problem goes away - at least you will know the cause...

If your non-VBA Pivot is linked to the filtered range then it may not be valid anymore...

After filtering the sheet , refresh the pivot and see what happens?

Are your slicers also setup using VBA? The error you mentioned seems to suggest that.

Can you share the file?

Answer 2

Hi Sheeloo

Thank you for your prompt reply. However, I cannot remove the filter from P41:P59 or my vba pivot chart will not work as intended.

Would there be another solution? 

Once again, thank you,

Marcus

Answer 3

Try to clear the filter on the range P41:P59.

If that works then you may add the following line, to fix it in the macro also, at the place shown below;

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

        ActiveSheet.AutoFilter.ApplyFilter

        Ctr = 1

        For Each Cell In Range("P41:P59").SpecialCells(xlCellTypeVisible)

        ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Points(Ctr).SecondaryPlot = Cell.Value

        Ctr = Ctr + 1

    Next Cell

ActiveSheet.AutoFilter

End Sub