VBA - Save sheets whose names are in table to single pdf

[Olov]

Hello. I want to be able to print sheets whose names are in a table as a pdf and save it to a predetermined location.
I found this but I don't know how to proceed. Transfer Excel Table into a VBA array

 

[John_w]

Try this. The sheet names are in the first column of the first table on "Sheet1". Change the path string variable to suit.

Public Sub Save_Table_Sheets_As_PDFs()

    Dim path As String
    Dim table As ListObject
    Dim r As Long
    Dim ws As Worksheet
   
    path = "C:\Temp\Excel\PDF\"
   
    Set table = ThisWorkbook.Worksheets("Sheet1").ListObjects(1)
  
    With table
        For r = 1 To .DataBodyRange.Rows.Count
            Set ws = Nothing
            On Error Resume Next
            Set ws = ThisWorkbook.Worksheets(.DataBodyRange(r, 1).Value)
            On Error GoTo 0
            If Not ws Is Nothing Then
                ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=path & ws.Name & ".pdf", _
                    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
            End If
        Next
    End With
   
End Sub

 

[Olov]

Try this. The sheet names are in the first column of the first table on "Sheet1". Change the path string variable to suit.

Public Sub Save_Table_Sheets_As_PDFs()

    Dim path As String
    Dim table As ListObject
    Dim r As Long
    Dim ws As Worksheet
   
    path = "C:\Temp\Excel\PDF\"
   
    Set table = ThisWorkbook.Worksheets("Sheet1").ListObjects(1)

    Set ws = ThisWorkbook.Worksheets("John")
   
    With table
        For r = 1 To .DataBodyRange.Rows.Count
            Set ws = Nothing
            On Error Resume Next
            Set ws = ThisWorkbook.Worksheets(.DataBodyRange(r, 1).Value)
            On Error GoTo 0
            If Not ws Is Nothing Then
                ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=path & ws.Name & ".pdf", _
                    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
            End If
        Next
    End With
   
End Sub

Thanks John_w! It works. Is it possible to get all the pdfs into one directly in Excel? I want page numbering throughout the pdf.
/Olov

 

[John_w]

Sorry, I overlooked the single PDF requirement.

Public Sub Save_Table_Sheets_As_Single_PDF()

    Dim path As String
   
    path = "C:\Temp\"
   
    With ThisWorkbook.Worksheets("Sheet1").ListObjects(1)
        ThisWorkbook.Worksheets(Application.Transpose(.DataBodyRange.Columns(1))).Select  'sheet names in column 1
    End With
   
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=path & "All Table Sheets.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True 'False

End Sub

Assumes all sheet names exist.

 

[Olov]

Thanks for the help. Can you modify the code so that it works if not all sheets names exist?

 

[John_w]

Public Sub Save_Table_Sheets_As_Single_PDF()

    Dim path As String
    Dim table As ListObject
    Dim r As Long
    Dim ws As Worksheet
    Dim replaceSheet As Boolean
    
    path = "C:\Temp\"
    
    replaceSheet = True
    Set table = ThisWorkbook.Worksheets("Sheet1").ListObjects(1)
    With table
        For r = 1 To .DataBodyRange.Rows.Count
            Set ws = Nothing
            On Error Resume Next
            Set ws = ThisWorkbook.Worksheets(.DataBodyRange(r, 1).Value)
            On Error GoTo 0
            If Not ws Is Nothing Then
                ws.Select replaceSheet
                replaceSheet = False
            End If
        Next
    End With
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=path & "Sheets.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True 'False
    
    table.Parent.Select
    
End Sub

 

[Olov]

Public Sub Save_Table_Sheets_As_Single_PDF()

    Dim path As String
    Dim table As ListObject
    Dim r As Long
    Dim ws As Worksheet
    Dim replaceSheet As Boolean
   
    path = "C:\Temp\"
   
    replaceSheet = True
    Set table = ThisWorkbook.Worksheets("Sheet1").ListObjects(1)
    With table
        For r = 1 To .DataBodyRange.Rows.Count
            Set ws = Nothing
            On Error Resume Next
            Set ws = ThisWorkbook.Worksheets(.DataBodyRange(r, 1).Value)
            On Error GoTo 0
            If Not ws Is Nothing Then
                ws.Select replaceSheet
                replaceSheet = False
            End If
        Next
    End With
   
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=path & "Sheets.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True 'False
   
    table.Parent.Select
   
End Sub

Thanks John_w! It works. You have made my day easier!
/Olov

 

[Olov]

To John_W:
Hello again.
Can you change the code and enter choices, save as pdf or print?

 

[John_w]

Can you change the code and enter choices, save as pdf or print?

You could do this:

    Dim response As Variant
    response = MsgBox("Click Yes to Save as PDF, or No to Print, or Cancel", vbYesNoCancel, "Save as PDF or Print")
    If response = vbYes Then
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=path & "Sheets.pdf", _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True 'False
    ElseIf response = vbNo Then
        ActiveSheet.PrintOut
    End If

 

[Olov]

You could do this:

    Dim response As Variant
    response = MsgBox("Click Yes to Save as PDF, or No to Print, or Cancel", vbYesNoCancel, "Save as PDF or Print")
    If response = vbYes Then
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=path & "Sheets.pdf", _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True 'False
    ElseIf response = vbNo Then
        ActiveSheet.PrintOut
    End If

Hello again,
When I choose to print on the printer, only sheet 1 is printed. If I choose PDF, all are printed in the same PDF. Do you know why?