VBA to save tabs with common name into single PDF

Mtnfan

New Member
Joined
Oct 29, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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.

VBA Code:
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
----- --
 
Upvote 0

Forum statistics

Threads
1,224,876
Messages
6,181,520
Members
453,050
Latest member
Obil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top