Loop though slicers

Seamus2626
Seamus2626 used Ask the Experts™
on
Hi,

I have code that goes through steps through slicers and prints PDFs

The code has stopped stepping throgh the slicers and i cant work out why

Sub Print_PDF_Managed_Sector()
'
' Print_PDF Macro
'

'
Sheets("PCM Sales Manager Dashboard").Select



FileMonth = Format(ActiveSheet.Range("C23"), "mmm-yy")

  If ActiveSheet.Range("C92") = "0" Then
  
  Else

 Dim Start As Single
Start = Timer
'wait 0.5 secs as some graphs do not update quick enough
Do While Start + 0.1 > Timer
    DoEvents
Loop



ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "R:\abc\Monthly_Outputs\Horis Reporting\" & FileMonth & "\Output\" & ActiveSheet.Range("Q6").Value & " - " & ActiveSheet.Range("I6").Value & " - " & ActiveSheet.Range("E1") & " (" & FileMonth & ") - Managed .pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
        
        
        Sheets("Sales Manager Dashboard").Activate



End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
Hi,

I don't see any reference to slicers in this code

Regards

Author

Commented:
Apologies

Sub Step_Thru_Managed_Sector()
    Dim slItem As SlicerItem
    Dim i As Long
    
  Range("U23").Value = "=K2"
    
        ActiveWorkbook.RefreshAll
    
    
    
    
    
    Call UnhideAllSheets
  
  Call Hide_Sheets_Managed
    
  With ActiveWorkbook.SlicerCaches("Slicer_Sector")
        '--deselect all items except the first
        .SlicerItems(1).Selected = True
        For Each slItem In .VisibleSlicerItems
            If slItem.Name <> .SlicerItems(1).Name Then _
                slItem.Selected = False
                
        Next slItem
        
        
        
      Call Print_PDF_Managed_Sector
        '--step through each item and run custom function
        For i = 2 To .SlicerItems.Count
            .SlicerItems(i).Selected = True
            .SlicerItems(i - 1).Selected = False
            
            
             
            If ActiveSheet.Range("C92") = "0" Then
            
            Exit Sub
            
            Else
            
            
            Call Print_PDF_Managed_Sector
       End If
            
        Next i
        
    
        
End With
  
  
End Sub

Open in new window

Top Expert 2016

Commented:
Hi,

pls try

Sub Step_Thru_Managed_Sector()
    Dim slItem As SlicerItem
    Dim i As Long
    
    Range("U23").Value = "=K2"
    
    ActiveWorkbook.RefreshAll
    
    Call UnhideAllSheets
  
    Call Hide_Sheets_Managed
  
  
    With ActiveWorkbook.SlicerCaches("Slicer_Sector")
        For Idx = 1 To .SlicerItems.Count
            .ClearManualFilter
            For Each slItem In .VisibleSlicerItems
                If slItem.Name <> .SlicerItems(Idx).Name Then
                    slItem.Selected = False
                Else
                    slItem.Selected = True
                End If
            Next slItem
            If Idx >= 2 And ActiveSheet.Range("C92") = "0" Then
                Exit Sub
            Else
                Call Print_PDF_Managed_Sector
            End If
        Next

    End With
    
End Sub

Open in new window

EDIT corrected line 24

Regards
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I think the problem lies here in
Sub Print_PDF_Managed_Sector()
'
' Print_PDF Macro
'

'
Sheets("PCM Sales Manager Dashboard").Select



FileMonth = Format(ActiveSheet.Range("C23"), "mmm-yy")

  If ActiveSheet.Range("C92") = "0" Then
  
  Else

 Dim Start As Single
Start = Timer
'wait 0.5 secs as some graphs do not update quick enough
Do While Start + 0.1 > Timer
    DoEvents
Loop



ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "R:\abc\Monthly_Outputs\Horis Reporting\" & FileMonth & "\Output\" & ActiveSheet.Range("Q6").Value & " - " & ActiveSheet.Range("I6").Value & " - " & ActiveSheet.Range("E1") & " (" & FileMonth & ") - Managed .pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
        
        
        Sheets("Sales Manager Dashboard").Activate

End If

Open in new window


  If ActiveSheet.Range("C92") = "0" Then

There probably should be something to say if = "0" then move to next item on slicer

Thanks
Top Expert 2016
Commented:
then try

Sub Step_Thru_Managed_Sector()
    Dim slItem As SlicerItem
    Dim i As Long
    
    Range("U23").Value = "=K2"
    ActiveWorkbook.RefreshAll
    
    Call UnhideAllSheets
  
    Call Hide_Sheets_Managed
    
    With ActiveWorkbook.SlicerCaches("Slicer_Sector")
        For Idx = 1 To .SlicerItems.Count
            .ClearManualFilter
            For Each slItem In .VisibleSlicerItems
                If slItem.Name <> .SlicerItems(Idx).Name Then
                    slItem.Selected = False
                End If
            Next slItem
            If Idx > 1 And ActiveSheet.Range("C92") = "0" Then
            Else
                Call Print_PDF_Managed_Sector
            End If
        Next
    End With

End Sub

Open in new window

Regards

Author

Commented:
Legend!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial