Multiple Summary pages

jsteele

New Member
Joined
Jul 26, 2018
Messages
7
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.
 
Last edited:

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,999
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.

Code:
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

New Member
Joined
Jul 26, 2018
Messages
7
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,999
Click Debug on the error message and I guess that it highlights this line:

Code:
        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:
Code:
       Set slCache = .SlicerCaches(2)
 

Forum statistics

Threads
1,082,246
Messages
5,363,995
Members
400,772
Latest member
solbebe

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top