How to Save Multiple Tabs as PDFs using the Tab Name with a Macro Button

[BrettOlbrys1]

Hello. I have a workbook that has 6 tabs and currently I save each tab as its own PDF (using the name of the workbook AND the tab name) and I do this manually for all six tabs. I want to create a macro button, such as "SAVE PDFs", and have it save each tab as its own PDF without me having to do it manually.

For example, my workbook name is "Sales 2021" and my tabs are "Northeast", "Southeast", "Central", "Southwest", "Northwest", "Mountain". When I save each tab as a PDF, they would be named:

Sales 2021 - Northeast.pdf
Sales 2021 - Southeast.pdf
etc...

Is there a way to do this?

 

[BrettOlbrys1]

In the VBA script you provided, all of the tab names were separated by a space, such as "Northeast Southeast Central Southwest Northwest Mountain", so if my tab names have a space in their name, such as "Sales 2021", how does the VBA script need to be modified to recognize the tab name with the space "Sales 2021" rather than thinking it's two tabs with the names "Sales" and "2001"?

 

[John_w]

Using a space to separate each sheet name in the string is just a convenience; I could have used any character (e.g. comma) because your sheet names are single words.

For sheet names with a space or no space, change the InStr line to:

        If InStr(1, ":Sales 2020:Sales 2021:Northeast:Southeast:Central:Southwest:Northwest:Mountain:", ":" & ws.Name & ":", vbTextCompare) Then

Note how each sheet name in the 2nd argument (the string to search) starts and ends with a colon and the same character surrounds the 3rd argument (the string being sought). Colon is an invalid character in a sheet name, so you can't have a sheet named "Northeast:Southwest", which the above Instr function would otherwise match.