Dashboard publish

[tintin1012000]

Looking for vb to cycle through filters /,slicer in a pivot chart in sheet 1.After each seletion change in sheet 1 i need to publish sheet 2 as a static webpage where the name of the webpage is taken from a cell in sheet 2 which i have linked to the pivor in sheet 1.

So basically i have approx 10 different selections in pivot in sheet 1 which will output a chart /dashboard for each one . I have a main page with links to all 10 webpages for navigation.. we have office13

Anyone help with the vba to change pivot and publish cycles.
I can have a predefined list in sheet 1 if that helps

 

[Worf]

Hello

Are you working with OLAP data? The code below will tell.

I understand changes at Sheet 1 will reflect on Sheet 2, is this correct?

Sub SSlicerI()
Dim i%, s$, wb As Workbook
Set wb = ActiveWorkbook
s = " "
For i = 1 To wb.SlicerCaches.Count
    If wb.SlicerCaches(i).OLAP Then _
    s = s + " " + wb.SlicerCaches(i).Name + vbLf
Next
MsgBox s, , "Total caches: " + CStr(wb.SlicerCaches.Count)
End Sub

 

[tintin1012000]

no im not working with Olap will give above a try, thanks

 

[tintin1012000]

3 slicers returned in my workbook
im going to split the file into 3 workbooks with one slicer each

 

[Worf]

An example:

Sub publish()
Dim si As SlicerItem, i%, j%
With ActiveWorkbook.SlicerCaches("Slicer_YEAR")
    .ClearManualFilter
    For j = 1 To .SlicerItems.Count
        .ClearManualFilter
        For i = 1 To .SlicerItems.Count
            Select Case j = i
                Case True
                    ' no action
                Case False
                    .SlicerItems(i).Selected = False
            End Select
        Next
        With ActiveWorkbook.PublishObjects.Add(xlSourceSheet, "c:\test\" & _
        CStr(Sheets("sheet2").[f8].Value) & ".htm", "Sheet2", "", xlHtmlStatic, _
        "Slicer-Conn_23998", .SlicerItems(j).Name)
            .publish True
            .AutoRepublish = False
        End With
    Next
End With
End Sub