Loop though slicers

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

Seamus2626Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

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

Regards
Seamus2626Author 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

Rgonzo1971Commented:
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
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Seamus2626Author 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
Rgonzo1971Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Seamus2626Author Commented:
Legend!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.