VBA - slicers not working after Data Source change

[_matteo]

Dear all,

I'm using an excel workbook with several pivot tables in it.
As the work is continuously updating, I needed a piece of code that allowed me to update all the data source of all the pivot tables in just one click, so I used this piece of code.

Sub Change_Pivot_Source()

    Dim pt As PivotTable
    Dim sht As Worksheet

For Each sht In ActiveWorkbook.Sheets
    For Each pt In sht.PivotTables
        sht.Activate
      pt.ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Campaign report!R3C1:R12500C327")
      pt.PivotCache.Refresh

                
         
    Next pt
    Next sht

ActiveWorkbook.RefreshAll

End Sub

At the end of the job, I need to create several Slicers, to help users to navigate through the pivot tables.
Unfortunately, when I create the slicers, there's no way to connect them to multiple pivot tables.

The range is exactly the same for all the pivot, the data the tables show are updated and coherent, but Slicers don't seem to "understand".
If, just to try, I manually go to option->change data source and click "OK" (without changing anything) on two different pivots, then I can link them with the slicer.

What am I missing? Is there something I can add to the vba code?

Thanks a lot in advance!!

Matteo

 

[RoryA]

You're creating a separate cache for each pivot which is why you can't link the slicers to all of them.

May I ask why you need to update the data source in code, rather than using a table or dynamic named range?

 

[_matteo]

You're creating a separate cache for each pivot which is why you can't link the slicers to all of them.

May I ask why you need to update the data source in code, rather than using a table or dynamic named range?

Hi, thanks for your reply!

I do not create a named range or a table because I'm a total noob and I never used them
I'll try to understand how to set a range, but at least now I know why the slicers do not work, it's totally clear.

 

[_matteo]

OK,

i created the table and i named it "Database"
Next thing to do, in my opinion, is manually set "Database" database as data source, once for all.
Because if i run a macro I come back into the same issue, with one cache for each pivot.

I have a problem, I defined the table with around 12000 rows (because I will need them in the future), but when I try to refresh Excel shows the "not available resources" message.
This never happened when I defined this range in the "old way", setting for instance the range as A3:CX12000

I know that dynamic named range could help, but I do not clearly understand how it works.

 

[RoryA]

A Table will automatically expand as you add rows below it, so you do not need to make it any larger than the data in it.

 

[_matteo]

A Table will automatically expand as you add rows below it, so you do not need to make it any larger than the data in it.

This will be really useful!

Unfortunately, at the moment, if I set 10000 rows (tried with fake data) the "refresh all" function crashes due to lack of resources.