excel - VBA code loop through all items in a slicer and print multiple sheets to PDF - Stack Overflow

admin管理员组

文章数量:1346336

I created 6 Excel sheets with pivot tables and charts all connected to one pivot table with a slicer. The slicer is in the sheet "Dashboard 1" and is named "Slicer_EnrollDistrictNumber". I am trying to write VBA code that will cycle through all the items in that slicer, print multiple sheets ("Dashboard 1" through "Dashboard 6") to PDF, and save to a folder. The code I have written prints the first item in the slicer but then I get an error that says "Error selecting slicer number:2" and the macro stops. Any help figuring out how to make it continue through the slicer items would be greatly appreciated. Here is the code I have:

Sub GenerateDistrictPDFs()
    ' Declare variables
    Dim wsData As Worksheet
    Dim slicerCache As slicerCache
    Dim slicerItem As slicerItem
    Dim dashboards As Variant
    Dim pdfPath As String
    Dim districtNumber As Integer
    
    ' Set the dashboards array with the names of the sheets to be printed
    dashboards = Array("Dashboard 1", "Dashboard 2", "Dashboard 3", "Dashboard 4", "Dashboard 5", "Dashboard 6")
    
    ' Set the data worksheet to "Dashboard 1"
    Set wsData = ThisWorkbook.Sheets("Dashboard 1")
    
    ' Activate the "Dashboard 1" worksheet
    wsData.Activate
    
    ' Set the slicer cache to the slicer named "Slicer_EnrollDistrictNumber"
    On Error Resume Next
    Set slicerCache = ThisWorkbook.SlicerCaches("Slicer_EnrollDistrictNumber")
    On Error GoTo 0
    
    ' Check if the slicer cache was set correctly
    If slicerCache Is Nothing Then
        MsgBox "Slicer 'Slicer_EnrollDistrictNumber' not found. Please check the slicer name.", vbCritical
        Exit Sub
    End If
    
    ' Loop through each slicer item in the slicer cache
    For Each slicerItem In slicerCache.SlicerItems
        ' Check if the slicer item can be selected
        If slicerItem.HasData Then
            ' Select the current slicer item
            On Error Resume Next
            slicerItem.Selected = True
            If Err.Number <> 0 Then
                MsgBox "Error selecting slicer item: " & slicerItem.Name & ". Please check the slicer item.", vbCritical
                Err.Clear
                On Error GoTo 0
                Exit Sub
            End If
            On Error GoTo 0
            
            ' Get the district number from cell A3 on the "Dashboard 1" sheet
            districtNumber = wsData.Range("A3").Value
            
            ' Set the path for the PDF file to be saved
            pdfPath = "C:\Users\Child Count District PDFs\" & districtNumber & ".pdf"
            
            ' Print the dashboards to a PDF file
            On Error Resume Next
            ThisWorkbook.Sheets(dashboards).Select
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:=pdfPath, Quality:=xlQualityStandard
            If Err.Number <> 0 Then
                MsgBox "Error exporting PDF for district: " & districtNumber & ". Please check the file path and permissions.", vbCritical
                Err.Clear
            End If
            On Error GoTo 0
            
            ' Move to the next slicer item without deselecting the current one
        End If
    Next slicerItem
End Sub

本文标签： excelVBA code loop through all items in a slicer and print multiple sheets to PDFStack Overflow