New to VBA and Powerpivot. Trying to automate slicer selections. I have a city slicer with 5 cities within that slicer. Another worksheet (CityList) contains data for each city that changes on each selection. I'd like the VBA to loop through each city, which would then update the CityList worksheet and export an excel spreadsheet for each "citylist" data into different worksheets. The looping part seems to work, but unsure of how to export each worksheet to a different file. Any help appreciated.
Sub GetSlicerValues()
Dim oSL As SlicerCacheLevel
Dim oSI As SlicerItem
Dim wb As Workbook
Set wb = Workbooks.Add
With ActiveWorkbook.SlicerCaches("Slicer_City")
For Each oSL In .SlicerCacheLevels
For Each oSI In oSL.SlicerItems
.VisibleSlicerItemsList = Array(oSI.Name)
ActiveWorkbook.Sheets("CityList").Copy Before:=wb.Sheets(1)
wb.SaveAs "C:\Test.xlsm"
Next oSI
Next oSL
End With
ActiveWorkbook.SlicerCaches("Slicer_City").ClearManualFilter
End Sub
Sub GetSlicerValues()
Dim oSL As SlicerCacheLevel
Dim oSI As SlicerItem
Dim wb As Workbook
Set wb = Workbooks.Add
With ActiveWorkbook.SlicerCaches("Slicer_City")
For Each oSL In .SlicerCacheLevels
For Each oSI In oSL.SlicerItems
.VisibleSlicerItemsList = Array(oSI.Name)
ActiveWorkbook.Sheets("CityList").Copy Before:=wb.Sheets(1)
wb.SaveAs "C:\Test.xlsm"
Next oSI
Next oSL
End With
ActiveWorkbook.SlicerCaches("Slicer_City").ClearManualFilter
End Sub
