Select Slicer Selections in turn

adambc

Active Member
Joined
Jan 13, 2020
Messages
368
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I have inherited a report that has 2 Worksheets ...

WS1 = multiple Pivot Tables
WS2 = 2 Slicers and Report output

Slicer1 = Year
Slicer2 = Month

I have some VBA code to print the active WS to a PDF file (with a unique filename) - in WS2 I want to cycle through the Slicer2 selections, each time invoking my code ie to print to 12 PDF files for Jan-Dec - this is how my Module starts, but I also need to deselect all but the current sI and you can't deselect all then select one (at the moment I just add the sI to the selection, rather than it being only the sI selected) - have looked at a number of ideas I've found but none quite fit - any ideas?

Thanks in advance

VBA Code:
Private Sub SlicerNamePDF()


Dim sC As SlicerCache
Dim sI As SlicerItem

Set sC = ActiveWorkbook.SlicerCaches("Slicer_Month")

For Each sI In sC.SlicerItems
     sI.Selected = True
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Try...

VBA Code:
Public Sub SlicerNamePDF()

    Dim sC                  As SlicerCache
    Dim i                   As Long
    Dim j                   As Long
  
    Set sC = ThisWorkbook.SlicerCaches("Slicer_Month")
  
    With sC.SlicerItems
        For i = 1 To .Count
            sC.ClearAllFilters
            For j = 1 To .Count
                If .Item(j).Name <> .Item(i).Name Then
                    .Item(j).Selected = False
                End If
            Next j
            'code for printing to PDF file
        Next i
    End With
  
    sC.ClearAllFilters
  
End Sub

Hope this helps!
 
Last edited:
Upvote 0
Try...

VBA Code:
Public Sub SlicerNamePDF()

    Dim sC                  As SlicerCache
    Dim i                   As Long
    Dim j                   As Long
   
    Set sC = ThisWorkbook.SlicerCaches("Slicer_Month")
   
    With sC.SlicerItems
        For i = 1 To .Count
            sC.ClearAllFilters
            For j = 1 To .Count
                If .Item(j).Name <> .Item(i).Name Then
                    .Item(j).Selected = False
                End If
            Next j
        Next i
        'code for printing to PDF file
    End With
   
    sC.ClearAllFilters
   
End Sub

Hope this helps!
@Domenic

Many thanks ...

Looks good - I won't be able to test it out for a few days, but when I have, I'll let you know how it went ...

One question ...

Should the 'code for printing to PDF file go BEFORE Next i - otherwise won't the code loop until .Count (December) before invoking the printing code?

Thanks again ...
 
Upvote 0
Yes, that's exactly right. I have edited my post accordingly.

Thanks for catching it.

Cheers!
 
Upvote 0
Solution
@Domenic

Works great - although it takes its time due to the For j = 1 To .Count iterations (12 * 12) ...

Any thoughts on how I could speed it up (not critical, but nice to have)?

Thanks again ...
 
Upvote 0
If you haven't already done so, try setting Application.ScreenUpdating to False at the beginning of the code...

VBA Code:
 Application.ScreenUpdating = False

Then, back to True at the end of the code...

VBA Code:
 Application.ScreenUpdating = True
 
Upvote 0
If you haven't already done so, try setting Application.ScreenUpdating to False at the beginning of the code...

VBA Code:
 Application.ScreenUpdating = False

Then, back to True at the end of the code...

VBA Code:
 Application.ScreenUpdating = True
@Domenic

Thanks for this ...

I've hit another problem that you might be able to help with ...

The report I'm applying this to is not my report! It's been set up with 19 Pivot Tables on the same Worksheet all linked to the (Jan - Dec) Slicer via the same Pivot Cache. The problem is that they've been positioned on the Worksheet in their collapsed/filtered state, which means that when sC.ClearAllFilters runs, they expand to their maximum size and I get the overlapping PT problem! I know I could go in and manually add blank lines between PTs (painful!), or move the PTs to separate Worksheets (but it's not my report!), but is there a different way?

Thanks ...
 
Upvote 0
I would suggest that you ask whoever is preparing the report to do so appropriately.
 
Upvote 0
Echoes my own thoughts exactly (except I was less restrained!)

I’ve now been handed another report with virtually the same requirement which has 46 PTs on the same WS (and I bet they weren’t positioned in their expanded state either!)

Ho hum, guess the requirement will have to wait until the reports have been fixed?!!!

Thanks for replying anyway ...
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

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
Back
Top