Multiple Summary pages

[jsteele]

Here is an example of a dashboard I created and I need help merging multiple dashboards into one PDF in excel.

https://pasteboard.co/HNV3cgL.png
This dashboard is a summary for one employee, but we have 50 employees. To change the employee to another, I am using a slicer, which then changes the data for the selected employee.

What I would like to produce is a dashboard page for each employee without having to go through all 50, print each, then combine, scan, and make a PDF. There must be an easier way.

-If the image is not showing correctly, This is a summary page with multiple graphs/charts. Each chart is linked to the slicer, so the slicer changes all of the charts.

 

[John_w]

See if this macro works for you. Test it on a copy of your workbook, containing only 5 employees, say. I've assumed that the dashboard is the first sheet in the workbook (the Worksheets(1) line) and the slicer is the first slicer in the workbook (the .SlicerCaches(1) line). The PDF file is created in the same folder as the workbook.

Option Explicit

Public Sub Create_PDF_From_Slicer_Items()

    Dim wb As Workbook
    Dim dataSheet As Worksheet
    Dim tempSheet As Worksheet
    Dim slCache As SlicerCache
    Dim slItem As SlicerItem, slMatch As SlicerItem
    Dim outputPDFfile As String
    Dim replaceSelectedSheet As Boolean
    Dim originalSheetsCount As Long
    Dim i As Long
    
    Set wb = ActiveWorkbook
    With wb
        outputPDFfile = .Path & "\Employee Reports.pdf"       'file name of output PDF file
        Set dataSheet = .Worksheets(1)                      'data (slicer output) is on the first sheet
        Set slCache = .SlicerCaches(1)
        originalSheetsCount = .Worksheets.Count
    End With
   
    Application.ScreenUpdating = False
    
    'Loop through each slicer item
    
    For Each slItem In slCache.SlicerItems
        
        'Show all items to start
        slCache.ClearManualFilter
            
        'Select the next slicer item to update the data sheet with that item
        For Each slMatch In slCache.SlicerItems
            If slItem.Name = slMatch.Name Then slMatch.Selected = True Else: slMatch.Selected = False
        Next

        'Copy the current data sheet to a new sheet
        dataSheet.Copy after:=wb.Worksheets(wb.Worksheets.Count)
        Set tempSheet = wb.Worksheets(wb.Worksheets.Count)
        tempSheet.Name = slItem.Name
        
        'Delete the slicer shape on the new sheet - don't want it in the PDF file
        tempSheet.Shapes.Range(1).Delete
        
    Next
    
    slCache.ClearManualFilter
    
    With wb
    
        'Save all newly added sheets as a single PDF file
        
        replaceSelectedSheet = True
        For i = originalSheetsCount + 1 To .Worksheets.Count
            .Worksheets(i).Select replaceSelectedSheet
            replaceSelectedSheet = False
        Next
        .ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=outputPDFfile, _
            Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        dataSheet.Select True
    
        'Delete all the added sheets
        
        Application.DisplayAlerts = False
        For i = .Worksheets.Count To originalSheetsCount + 1 Step -1
            .Worksheets(i).Delete
        Next
        Application.DisplayAlerts = True
    
    End With
    
    Application.ScreenUpdating = True
    
    MsgBox "Created " & outputPDFfile
    
End Sub

 

[jsteele]

Thanks for taking time to create this Macro. Yes I have the dashboard as the first tab. However, what I'm not sure about is the slicer that is referenced. Not only do I have one for employees, there is another slicer for Month.

I did run the macro, as is though, after running for a short time I got Run-time error '1004'. "You typed an invalid name for a sheet or chart..................."

 

[John_w]

Click Debug on the error message and I guess that it highlights this line:

        tempSheet.Name = slItem.Name

That line attempts to change the default sheet name that Excel assigns to the copied sheet to the employee name. If the employee name contains characters that are invalid for a sheet name then you'll get that error.

Try deleting that line.

For the slicers, if the first slicer isn't the employees slicer then change the line to:

       Set slCache = .SlicerCaches(2)