VBA to save tabs with common name into single PDF

[Mtnfan]

I prepare a spreadsheet for a committee that tracks performance a few times per year.
I have 4 tabs that each track a separate component pertaining to the performance of one person.
Each person has the following sheets: Cover sheet (with basic background info), KPI (key performance indicators), Productivity, Feedback

The tabs are titled like this: Last name (or sometimes "Lastname Firstinitial") - Worksheettitle

Ferguson - Cover
Ferguson - KPI
Ferguson - Productivity
Ferguson - Feedback
Smith A - Cover
Smith A - KPI
Smith A - Productivity
Smith A - Feedback

There are usually at least 20 and sometimes 30 separate employees (80-120 tabs).

I want to write a VBA that will group worksheets by employee and save them as a separate PDF for each employee in the sheet.
However, since the worksheet names have more than the employee's name in common, I wasn't sure how to avoid grouping the sheets by the other common elements. In other words, I don't want to end up with a PDF that has all the "cover" sheets and a second one with all the "KPI" sheets and so on. Is there a strategy to do this? One thing I thought I might do is establish a single worksheet "legend" that lists employees and the nomenclature used for sheet naming. For example:

Bob Ferguson - Ferguson
Alvin Smith - Smith A

The VBA would then group any sheets with names containing the shorthand version of the employee name as listed.

Thanks in advance for your help!

 

[DanteAmor]

Thanks for posting on the forum!

Let's analyze what you have:

The tabs are titled like this: Last name (or sometimes "Lastname Firstinitial") - Worksheettitle
Ferguson - Cover
Ferguson - KPI
Ferguson - Productivity
Ferguson - Feedback

If all the sheets have a hyphen "-" between the employee name and Worksheettitle, then we can separate the common name of each employee and make a list of the employees (using a dictionary).

That way we get the names of the employees and generate a pdf for each employee, try the following macro, the pdf files will be saved in the same folder where you have your file with the macro.

Sub SaveTabsWithCommonName()
  Dim sh As Worksheet
  Dim wb1 As Workbook, wb2 As Workbook
  Dim dic As Object
  Dim ky As Variant, tbs() As Variant
  Dim n As Long
  
  Application.ScreenUpdating = False
  
  Set wb1 = ThisWorkbook
  Set dic = CreateObject("Scripting.Dictionary")
  
  For Each sh In Sheets
    If InStr(1, sh.Name, "-") > 0 Then
      ky = Trim(Split(sh.Name, "-")(0))
      dic(ky) = Empty
    End If
  Next
  
  For Each ky In dic.keys
    n = 0
    For Each sh In Sheets
      If Left(sh.Name, Len(ky)) = ky Then
        ReDim Preserve tbs(n)
        tbs(n) = sh.Name
        n = n + 1
      End If
    Next
    Sheets(tbs).Copy
    Set wb2 = ActiveWorkbook
    wb2.ExportAsFixedFormat Type:=xlTypePDF, Filename:=wb1.Path & "\" & ky, _
      Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    wb2.Close False
  Next
  
  Application.ScreenUpdating = True
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --