VBA Disconnecting Slicer, updating Pivottable source, reconnecting Slicer


New Member
Hi guys,

I'm running into a little but of a problem that I'm hoping someone here can help me with.

On a daily report that I run I have 8 different PivotCharts from 8 different PivotTables, all from the same data source but displaying different parts of it. I also have one slicer and one timeline that are used to filter all 8 charts.

Here is my problem: Given that the report is updated daily, I have written a macro that automatically updates the pivot tables to include the newly added data. When there is no slicer or timeline it runs fine however when I add them in it gives me a Run Time Error 5.

Here is my question: What would be the best method for writing a vba code that will 1) Disconnect 8 pivot tables from a slicer that already exists 2) Update the data range on the pivot tables 3) Reconnect each pivot table back to the original slicer?

I've seen a few solutions through research but nothing that's exactly what I need and I'm tearing my hair out trying to figure it out. Hoping there's an obvious solution.

Thanks in advance!

Some videos you may like

This Week's Hot Topics