Forum Discussion
Dynamic Sheet Navigation with Slicers – Best Approach?
Hi everyone,
I was wondering how I could build a setup where I can use a slicer to cycle through different tabs like in the image. I've been trying this for sometime now and can't seem to figure it out.
From what I gathered I don't have to make a table and then add a slicer since the example provided doesn't seem to use one either.
I have no idea where to start so if anyone could help me get in the right direction that would be much appreciated.
1. Create a pivot table: Select the data range and click “Insert” > “Pivot Table”.
Add the required fields to the pivot table.
2. Insert Slicer: Click on the pivot table and select “Analyze” > “Insert Slicer”.
Select the fields you want to filter and click “OK”.
3. Dynamic navigation using VBA: Press Alt + F11 to open the VBA editor.
Insert a new module and enter the following code:
vba
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim ws As Worksheet
Dim slicerCache As SlicerCache
Dim slicerItem As SlicerItem
Dim selectedItem As String
Set slicerCache = ThisWorkbook.SlicerCaches(“Slicer_FieldName”)
SelectedItem = slicerCache.SlicerItems(1).Name
For Each ws In ThisWorkbook.Worksheets
If ws.Name = selectedItem Then
ws.Activate
Exit For
End If
Next ws
End Sub
Replace Slicer_FieldName in the code with the actual slicer name.
4. Navigating with Hyperlinks: Create hyperlinks in the worksheet to other worksheets.
Use VBA scripts to dynamically update the hyperlinks based on the slicer selection.
5. Contact Microsoft Support: If none of the above methods work, it is recommended to contact Microsoft support team with detailed error messages and screenshots for further assistance.
