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?

 

[John_w]

Assign this macro to the button.

Public Sub Save_Each_Sheet_As_PDF()

    Dim p As Long, ws As Worksheet
    
    p = InStrRev(ThisWorkbook.FullName, ".") - 1
    For Each ws In ThisWorkbook.Worksheets
        ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Left(ThisWorkbook.FullName, p) & " - " & ws.Name & ".pdf", _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Next
    
End Sub

 

[BrettOlbrys1]

Thanks for the code. I forgot one thing though, I actually have like 20 tabs, but there are only those select few I want to create the PDFs for. Is there a way to only select certain tabs to save as a PDF? Maybe the tabs I want to print have some identifier in the name that makes them unique?

 

[John_w]

In that case we can list the sheet names you want to save, like this:

Public Sub Save_Each_Sheet_As_PDF()

    Dim p As Long, ws As Worksheet
    
    p = InStrRev(ThisWorkbook.FullName, ".") - 1
    For Each ws In ThisWorkbook.Worksheets
        If InStr(1, "Northeast Southeast Central Southwest Northwest Mountain", ws.Name, vbTextCompare) Then
            ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Left(ThisWorkbook.FullName, p) & " - " & ws.Name & ".pdf", _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        End If
    Next
    
End Sub

 

[BrettOlbrys1]

So, if I have different names that I want saved, all I need to do is modify this code to reflect the tab names?

If InStr(1, "Northeast Southeast Central Southwest Northwest Mountain", ws.Name, vbTextCompare) Then

 

[BrettOlbrys1]

I attempted to run the macro for the "SAVE PDFs" and nothing happened. I know the macro functionality is working because I have another macro that pulls data from a server and my file updates correctly, but the PDF macro isn't doing anything?

 

[John_w]

So, if I have different names that I want saved, all I need to do is modify this code to reflect the tab names?

If InStr(1, "Northeast Southeast Central Southwest Northwest Mountain", ws.Name, vbTextCompare) Then

Yes.

I attempted to run the macro for the "SAVE PDFs" and nothing happened. I know the macro functionality is working because I have another macro that pulls data from a server and my file updates correctly, but the PDF macro isn't doing anything?

Have you saved the macro workbook?

Try changing every occurrence of ThisWorkbook to ActiveWorkbook.

 

[BrettOlbrys1]

I figured out the issue. My tabs have spaces in the name, such as "Sales 2020" and "Sales 2021" and I guess the VBA code doesn't like spaces? It worked when I removed the spaces in the tab names and made sure the names in the code were exact.

Can tab names have spaces in the name or do I need to remove all spaces in names?

 

[BrettOlbrys1]

The functionality works GREAT though in any case, so thank you!

 

[John_w]

I figured out the issue. My tabs have spaces in the name, such as "Sales 2020" and "Sales 2021" and I guess the VBA code doesn't like spaces? It worked when I removed the spaces in the tab names and made sure the names in the code were exact.

Those sheet (tab) names are different to the ones listed in your OP.

Can tab names have spaces in the name or do I need to remove all spaces in names?

Yes, sheet names can have spaces.