VBA to split pivot table into sheets based on slicer

Switters123

New Member
Joined
Mar 30, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
I have VBA in "Workbook #1" that works for me: it creates new sheets in the workbook for each slicer item, for a slicer that is connected to a Power Pivot table. HOWEVER, when I try to run this in a different workbook with a different slicer, it errors out on the Set SCL line. I updated the slicer name (in two lines) to match the slicer name in Workbook #2; not sure what else is needed. Possibly the problem is because Workbook #2 is a standard pivot table and Workbook #1 is Power Pivot, but I don't know the corrective action that is needed. I don't want to use the Pivot Table Options >> Show Report Filter Pages tool because I lose my formatting when the new sheets are created.

Sub PPReportFilterPages2()

Dim counter As Long
Dim SC As SlicerCache
Dim SCL As SlicerCacheLevel
Dim SliceItem As String
Dim PPWS As Worksheet

Set SC = ActiveWorkbook.SlicerCaches("Slicer_Customer")
Set SCL = SC.SlicerCacheLevels(1)
Set PPWS = ActiveSheet

counter = 1

' Iterate through the slicer categories changing the PivotTable filter
While counter <= ActiveWorkbook.SlicerCaches("Slicer_Customer").SlicerCacheLevels.Item.Count

' Store the category name
SliceItem = SCL.SlicerItems(counter).Value

' Change the filter
SC.VisibleSlicerItemsList = SCL.SlicerItems(counter).Name

'Copy the filtered table to a new sheet
ActiveSheet.Copy after:=Sheets(Sheets.Count)

' Rename the sheet to the category (filter) name
Sheets(Sheets.Count).Name = SliceItem

' Uncomment the next line if you want to delete the slicer from the new sheet
ActiveWorkbook.SlicerCaches(ActiveWorkbook.SlicerCaches.Count).Delete

' Go back to the PivotTable sheet to select the next category
PPWS.Activate

counter = counter + 1

Wend

End Sub
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Switters123

New Member
Joined
Mar 30, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
I recreated the report in Power Pivot and it works. So I guess my problem is solved via a workaround.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,592
Members
417,224
Latest member
llama9207

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top