Slicer connection Greyed out

[santhosha]

I tried connecting the same slicer(s) to multiple pivot tables using the same source data. After I added a new pivot table and having the new pivot table selected, I went to the Slicer icon drop down and was going to select "Slicer connections", but it is grayed out.


Is there a step that I missed?


Thanks


Santhosh

 

[bobhc]

Good day santhosha


If the Slicers are greyed out you are probably inCompatibility Mode. To resolve this

go to Save As and save this copy in the new XLSX format.


It some times works to save your file with the correct file extension but with a new name

 

[Hui]

Santhosh


After save as new file type, close and re-start Excel

re-open your file

 

[rmadgula]

I have the same problem. I see the "Slicer Connections" grayed out and disabled. I have saved the file with a NEW name in XLSX format but it still doesnt seem to allow me to use the same slicer for two different Pivot tables. Do the pivot tables have to fullfill certain criteria for it to be enabled.

Any help here is appreciated!


Thank you

 

[jeffreyweir]

rmadgula: Check that the pivots share the same cache.


Here's some code I'm working on from time to time that reports on this (and a good deal besides) for each pivot in your workbook.


Copy it into a new code module in your workbook, run it, and check the CacheIndex column in the report that is generated. If the pivots share the same cache, slicers should work.

If they don't, you can use the code I wrote at http://blog.contextures.com/archives/2013/06/18/update-multiple-pivot-tables-20130618/#comment-44477 to syncronise pivot tables.

Option Explicit

Option Base 1
Sub PivotReport()

Dim pc As PivotCache
Dim pt As PivotTable
Dim lngPivots As Long
Dim lngPosition As Long
Dim wks As Worksheet
Dim varSettings As Variant
Dim bWorksheetExists As Boolean
Dim wksOutput As Worksheet
Dim rngOutput As Range
Dim loOutput As ListObject

' The purpose of lngPosition? I don't want to hard-code the array position of each element at this early development stage.
' So I just increment a counter, which means I can move these headings around, and as long as the correstponding
' code that records the actual attributes is in the same order, I don't have to renumber the array elements
' any time I make a change.
lngPosition = 1
ReDim varSettings(14, 1)
varSettings(lngPosition, 1) = "Pivot Name"
lngPosition = lngPosition + 1
varSettings(lngPosition, 1) = "Worksheet"
lngPosition = lngPosition + 1
varSettings(lngPosition, 1) = "Worksheet Protected?"
lngPosition = lngPosition + 1
varSettings(lngPosition, 1) = "CacheIndex"
lngPosition = lngPosition + 1
varSettings(lngPosition, 1) = "Pivot Address"
lngPosition = lngPosition + 1
varSettings(lngPosition, 1) = "RowGrand"
lngPosition = lngPosition + 1
varSettings(lngPosition, 1) = "ColumnGrand"
lngPosition = lngPosition + 1
varSettings(lngPosition, 1) = "MissingItemsLimit"
lngPosition = lngPosition + 1
varSettings(lngPosition, 1) = "RecordCount"
lngPosition = lngPosition + 1
varSettings(lngPosition, 1) = "MemoryUsed (kB)"
lngPosition = lngPosition + 1
varSettings(lngPosition, 1) = "SaveData"
lngPosition = lngPosition + 1
varSettings(lngPosition, 1) = "EnableRefresh"
lngPosition = lngPosition + 1
varSettings(lngPosition, 1) = "SourceData"
lngPosition = lngPosition + 1
varSettings(lngPosition, 1) = "EnableDrilldown"
lngPosition = lngPosition + 1

lngPivots = 1

For Each wks In ActiveWorkbook.Worksheets
    For Each pt In wks.PivotTables
        lngPosition = 1
        lngPivots = lngPivots + 1
        ReDim Preserve varSettings(14, lngPivots)
       
        With pt
            varSettings(lngPosition, lngPivots) = .Name
            lngPosition = lngPosition + 1
            varSettings(lngPosition, lngPivots) = wks.Name
            lngPosition = lngPosition + 1
            varSettings(lngPosition, lngPivots) = wks.ProtectContents
            lngPosition = lngPosition + 1
            varSettings(lngPosition, lngPivots) = .CacheIndex
            lngPosition = lngPosition + 1
            varSettings(lngPosition, lngPivots) = .TableRange2.Address
            lngPosition = lngPosition + 1
            varSettings(lngPosition, lngPivots) = .RowGrand
            lngPosition = lngPosition + 1
            varSettings(lngPosition, lngPivots) = .ColumnGrand
            lngPosition = lngPosition + 1
            varSettings(lngPosition, lngPivots) = .PivotCache.MissingItemsLimit
            lngPosition = lngPosition + 1
            varSettings(lngPosition, lngPivots) = .PivotCache.RecordCount
            lngPosition = lngPosition + 1
            varSettings(lngPosition, lngPivots) = .PivotCache.MemoryUsed / 1000
            lngPosition = lngPosition + 1
            varSettings(lngPosition, lngPivots) = .SaveData
            lngPosition = lngPosition + 1
            varSettings(lngPosition, lngPivots) = .PivotCache.EnableRefresh
            lngPosition = lngPosition + 1
            varSettings(lngPosition, lngPivots) = .SourceData
            lngPosition = lngPosition + 1
            varSettings(lngPosition, lngPivots) = .EnableDrilldown
            lngPosition = lngPosition + 1
       
        End With
    Next pt
Next wks

'Create a worksheet to dump the output into
Set wksOutput = Sheets.Add
Set rngOutput = Range("A1").Resize(UBound(varSettings, 2), UBound(varSettings, 1))
rngOutput.Value = Application.Transpose(varSettings)
Set loOutput = wksOutput.ListObjects.Add(xlSrcRange, rngOutput, , xlYes)

End Sub

 

[Andrew-1]

To Jeffreyweir: please could you repost your Sub PivotReport because the third line before end sub contains &#34 and I can't work out what it should be.

 

[Luke M]

To Jeffreyweir: please could you repost your Sub PivotReport because the third line before end sub contains &#34 and I can't work out what it should be.

Hi Andrew,

Looks like the post got corrupted when we did a forum migration a few years back. I've gone through and fixed his post.

 

[NARAYANK991]

Hi Andrew ,

To add to what Luke has mentioned , see this post for more information :

http://chandoo.org/forum/threads/find-the-hidden-links-in-excel-work-book.9040/#post-130981

Narayan