Update Pivot Table Data Source with Multiple Slicers

Hello All,

I am using a raw data (source data) which fluctuates every week from 60,000 lines to 90,000 lines. I have created around 9 different pivot tables and 7 different slicers by using the same raw data. All the pivot tables have been connected to all the slicers.
Every time the raw data changes, I have to first go through and disconnect every single slicer, refresh the pivot tables, then reconnect every slicer. Not a viable solution for my clients.
I found below mentioned code to disconnect all slicers but that is only half the issue. So I'm not sure where to go from here. VBA code to change the data source seems to run into the same issue and it crashes when it runs into a pivot that is connected to multiple slicer.

Sub Disconnect_Slicers()

Dim oSliceCache As SlicerCache
Dim PT As PivotTable
Dim i As Long

For Each oSliceCache In ThisWorkbook.SlicerCaches
    With ActiveWorkbook.SlicerCaches(oSliceCache.Name).PivotTables
        For i = .Count To 1 Step -1
            .RemovePivotTable (.Item(i))
        Next i
    End With
Next oSliceCache

End Sub


The last thought I had was maybe somehow looping through the slicers and storing their names and settings in some sort of array, disconnecting them, updating the data source and then reconnecting them, or similarly looping first through the Pivot Table, finding any slicers connected to it and then doing those same steps, but I have to admit I haven't used arrays extensively and never with slicers so I'm not sure if this is a good idea or if so how to even get started on it.
I am asking the Excel/VBA geniuses out there, to help me with a code which changes the data source and refresh the pivot table by disconnecting and reconnecting slicers automatically.

Appreciate any help or advice.

Looking forward for the positive response!

Thanks!
Ganesh
Ganesh VijaykumarAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
Can't you use a Table as the data source?
Ganesh VijaykumarAuthor Commented:
@Rory Archibald : I am sorry i didn't get you. Table in MS access? To be honest  I am not well versed in Ms accsess.
Rory ArchibaldCommented:
No, a Table in Excel rather than a regular range.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Ganesh VijaykumarAuthor Commented:
If I format the data as table will the range automatically change in pivot tables?
Rory ArchibaldCommented:
You'll need to change the data source for the pivots one time to use the table name as the source. After that, you can just update the table and then refresh the pivots.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
C. Blaise MitsutamaLMS Administrator | Instructional DesignerCommented:
Ganesh VijaykumarAuthor Commented:
Thanks Rory Archibald it worked for me.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.