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
6,043
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
6,043
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,084,738
Messages
5,379,524
Members
401,611
Latest member
CandaceR68

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top