Unable to Re-connect Slicers after Disconection and Data Refresh (VBA, Excel 2010)

[shaclark]

Greetings, <br>
I cannot thank you all enough for the many times your collective wisdom have provided me with critical knowledge in time of need. I am a professional with some programming background, not a programming professional by any means. I therefore apologize if my question is idiotic (and I'm the only one here who doesn't realize it). <br>
I am working on a spreadsheet template for a data dashboard. Weekly data comes in--no more than 30000 rows and 15 columns--the pivot tables need to be refreshed, the value fields updated (each week, columns are added to the raw data, so "Sum of 2/22/14" has to become "Sum of 2/29/14," etc.). <br>
To summarize multiple slices of data simultaneously, I have used three slicers which I successfully--thanks to all of you--connected to twelve (not a typo) tiny pivot tables. <br>
I used the "create one pivot table, add the slicers, then use <em>Slicer Connections</em> from the ribbon, check the boxes to connect the slicers so all the pivots change together" method. It worked beautifully. <br>
I then discovered that, to change the source data and refresh, I must first disconnect the shared slicers. I found this piece of code (here, I believe) which works:
<br>

<br>
For y = 1 To 7<br>
With ActiveWorkbook.SlicerCaches(y).PivotTables<br>
    For x = .Count To 1 Step -1<br>
        .RemovePivotTable (x)<br>
    Next x<br>
End With<br>
Next y<br>

<br>

(FYI, there is a pivot chart (with assoc. table) in this workbook on different worksheets, which I have to maintain. It pulls from the same data; I update it when I update my new stuff... that's why I said 3 slicers, but code refers to 7. In the event, my slicers are attached to SlicerCaches 5 through 7).<br>
I was able to reconnect slicers 1 through 4 (which control the pivot chart) to their one PT with no trouble, using this method:
<br>

<br>
For d = 1 To 4<br>
    With ActiveWorkbook.SlicerCaches(d)<br>
        For Each slcer In .Slicers<br>
            slcer.SlicerCache.PivotTables.AddPivotTable (Sheets("PivotTable").PivotTables("PivotTable3"))<br>
        Next<br>
    End With<br>
Next d<br>

<br>
I am completely at a loss, however, regarding why I cannot reconnect my three slicers, via VBA or manually. <br>
When I try to manually re-connect, just as proof of concept, the Slicer Connections dialog is now blank. It isn't disabled, as when you try to connect a pivot table with a different cache. It's just blank. I even tried deleting my original 3 slicers, selecting one of my 12 pivots, adding the slicers and then trying to manually connect: failure. The dialog is still blank. <br>
In VBA, I have tried a number of methods, at last pure desperation:
<br>

<br>
For d = 1 To 4<br>
    With ActiveWorkbook.SlicerCaches(d)<br>
        For Each slcer In .Slicers<br>
            slcer.SlicerCache.PivotTables.AddPivotTable (Sheets("Dashboard").PivotTables("PivotTable1"))<br>
            slcer.SlicerCache.PivotTables.AddPivotTable (Sheets("Dashboard").PivotTables("PivotTable2"))<br>
            slcer.SlicerCache.PivotTables.AddPivotTable (Sheets("Dashboard").PivotTables("PivotTable3"))  'ETC.<br>
        Next<br>
    End With<br>
Next d<br>

<br>
I have previously tried FOR EACH, WITH, and a simple FOR NEXT prior to the stupid "name each explicitly" approach here. Nothing works. <br>
The slicers connect only to the one pivot table from which they are originally created. When VBA tries to reconnect the slicers to the second pivot table, I get this error:<br>
<strong>Run-time error '1004':<br>
Application-defined or object-defined error</strong><br>
I take this to be the VBA equivalent of the Excel Slicer Connections dialog being blank. <br>
I have searched MSDN, here, and several other forums, but have not been smart enough to figure out what I've done wrong. Perhaps I don't recognize code that will solve the problem when I see it. <br>
Question: Did I disconnect the slicers incorrectly, such that I now can't re-connect them? If so, can anyone share the right way to do it?<br>
Question: If I disconnected correctly, what am I missing that is preventing even a manual re-connection?<br>
Here are the exact steps of my code, in case this will help you help me:<br>
<ol class="decimal"><li>Detect the lower-right corner of the imported dataset.</li>
<li>Disconnect the slicers in the workbook.</li>
<li>Detect the two active value fields and disable (xlHidden) them.</li>
<li>Redefine the Pivot Tables' data source (PT.ChangePivotCache ActiveWorkbook.PivotCaches.Create) using the values from step 1.</li>
<li>Detect the names of the two value fields to use for the new data and enable them (AddDataField).</li>
<li>Reconnect the pivot chart's four slicers.</li>
<li>Reconnect my three slicers to my multiple pivot tables.</li>
<li>Refresh all pivot tables in the workbook. </li>
</ol><br>
Everything above works, except for step 7. <br>
Thank you all, very sincerely, in advance for your time and expertise. <br>
Scott<br>

 

[shaclark]

I can't thank Tracy at MrExcel.com enough for her expert assistance. In the event, I was being an idiot and was trying to attach slicers created from one PivotCache to PivotTables associated with another. Not. Going. To. Work.

She very correctly suggested that I use something like (this is my code, not hers... trust me, hers was much cleaner!):

PivotName = "dbPivotTable" & Right(Str(twoPivots), 2)
            TableLocation = "DASHBOARD!R" & Right(Str(pivotRow), 2) & "C" & Right(Str(pivotColumn), 2)
            ActiveWorkbook.Worksheets("PivotTable").PivotTables("PivotTable3").PivotCache. _
                    CreatePivotTable TableDestination:=TableLocation, TableName:= _
                    PivotName, DefaultVersion:=xlPivotTableVersion12

to create my subordinate pivot tables from the main table so they're all using the same pivotcache. It was then very easy to use

        Set scp = ActiveWorkbook.SlicerCaches.Add(Source:=Worksheets("Dashboard").PivotTables("dbPivotTable18"), sourcefield:="Division")
        Set slp = scp.Slicers.Add(ActiveSheet, Name:="slicer_db_Division", Caption:="Division", Top:=ActiveSheet.Range("E2").Top, _
            Left:=ActiveSheet.Range("E2").Left + 5, Width:=ActiveSheet.Range("E2:G2").Width, Height:=ActiveSheet.Range("E2:E14").Height)
        
        Set scp = ActiveWorkbook.SlicerCaches.Add(Source:=Worksheets("Dashboard").PivotTables("dbPivotTable18"), sourcefield:="WFUGrouping")
        Set slp = scp.Slicers.Add(ActiveSheet, Name:="slicer_db_WFUGrouping", Caption:="WFU Group", Top:=ActiveSheet.Range("H2").Top, _
            Left:=ActiveSheet.Range("H2").Left + 20, Width:=ActiveSheet.Range("H2:L2").Width, Height:=ActiveSheet.Range("H2:H14").Height)
        
        Set scp = ActiveWorkbook.SlicerCaches.Add(Source:=Worksheets("Dashboard").PivotTables("dbPivotTable18"), sourcefield:="Org Unit")
        Set slp = scp.Slicers.Add(ActiveSheet, Name:="slicer_db_OrgUnit", Caption:="Org Unit", Top:=ActiveSheet.Range("M2").Top, _
            Left:=ActiveSheet.Range("M2").Left + 38, Width:=ActiveSheet.Range("M2:O2").Width, Height:=ActiveSheet.Range("M2:M14").Height)
        
        ActiveWorkbook.SlicerCaches("Slicer_WFUGrouping1").Slicers("slicer_db_WFUGrouping").Style = "SlicerStyleLight2"
        ActiveWorkbook.SlicerCaches("Slicer_Org_Unit1").Slicers("slicer_db_OrgUnit").Style = "SlicerStyleLight3"

to add the slicers.

Works perfectly! Even more valuable to me is that she helped me get my thinking correct on the whole topic. Thank you!

 

[starl]

awww. You're welcome, Scott. Glad to help. And thank you for returning and posting the solution.

 

[wiz329]

Hi there - I'm having a very similar problem which you might be able to help me with.

I'm trying to create 2 separate dashboards which consists of multiple graphs, linked to about 10-15 different pivot tables, which all come from the same source.

I'm in the earlier stages of the problem, so I haven't had to deal with the refreshing problem. However, I'm trying to link all of my graphs together (on one of the two dashboards) using a single slicer connected to about 5 different pivot tables on different sheets. The problem I'm having is that not all of the pivot tables show up when I try to connect them to the slicer.

After some searching on google, I suspect that the problem may have something to do with the pivot caches, but I'm not exactly sure how these work. Perhaps you might be able to help?

Thanks!