Alter convert to PDF Code to include more tabs?

[Coyotex3]

Hello guys, I have this code right here which copies each individual sheet and converts it to PDF

Dim Folder_Path As String

With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select the Folder Path"

If .Show = -1 Then Folder_Path = .SelectedItems(1)

End With

Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets

    sh.ExportAsFixedFormat xlTypePDF, Folder_Path & Application.PathSeparator & sh.Name & ".pdf"
Next

MsgBox "Done"


End Sub

This code currently takes each sheet and converts it to individual PDFs. So if I have "Sheet1" and "Sheet2" it will make two pdfs. I'm going to have another tab called "Back-Up" and I would like for the Macro to convert "Sheet11" and "Back-Up" tab and convert it to one PDF and then "Sheet2" and "Back-Up" as one PDF etc.

Is there a way of getting this one?

 

[Domenic]

The following macro will loop through each worksheet within the active workbook, and for each worksheet except for "Back Up" it will group each worksheet with the "Back Up" sheet, and then create a single PDF for the grouped worksheets using the ExportAsFixedFormat method of the ActiveSheet object. Note that I also amended the code that prompts the user to select a folder so that if the user cancels the prompt it will exit the sub.

Sub test()

    Dim Folder_Path As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select the Folder Path"
        If .Show <> -1 Then Exit Sub
        Folder_Path = .SelectedItems(1)
    End With
   
    Dim backupSheet As Object
    Set backupSheet = ActiveWorkbook.Sheets("Back Up")
   
    Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Worksheets
        If sh.Name <> backupSheet.Name Then
            Sheets(Array(sh.Name, backupSheet.Name)).Select
            ActiveSheet.ExportAsFixedFormat xlTypePDF, Folder_Path & Application.PathSeparator & sh.Name & ".pdf"
        End If
    Next
   
    MsgBox "Done"

End Sub

Hope this helps!

 

[Coyotex3]

The following macro will loop through each worksheet within the active workbook, and for each worksheet except for "Back Up" it will group each worksheet with the "Back Up" sheet, and then create a single PDF for the grouped worksheets using the ExportAsFixedFormat method of the ActiveSheet object. Note that I also amended the code that prompts the user to select a folder so that if the user cancels the prompt it will exit the sub.

Sub test()

    Dim Folder_Path As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select the Folder Path"
        If .Show <> -1 Then Exit Sub
        Folder_Path = .SelectedItems(1)
    End With
  
    Dim backupSheet As Object
    Set backupSheet = ActiveWorkbook.Sheets("Back Up")
  
    Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Worksheets
        If sh.Name <> backupSheet.Name Then
            Sheets(Array(sh.Name, backupSheet.Name)).Select
            ActiveSheet.ExportAsFixedFormat xlTypePDF, Folder_Path & Application.PathSeparator & sh.Name & ".pdf"
        End If
    Next
  
    MsgBox "Done"

End Sub

Hope this helps!

Domenic, thank you so much for this!! This is the right answer.

Minor request(if possible) is there a way of getting the "Back Up" tab to be the 2nd page of the PDF file as opposed to the 1t?

 

[Domenic]

Domenic, thank you so much for this!! This is the right answer.

You're very welcome, glad I could help.

Minor request(if possible) is there a way of getting the "Back Up" tab to be the 2nd page of the PDF file as opposed to the 1t?

Simply move your "Back Up" sheet/tab at the end of your sheets.

 

[Coyotex3]

You're very welcome, glad I could help.

Simply move your "Back Up" sheet/tab at the end of your sheets.

Thank you so much once again! Works like a charm.

 

[smozgur]

Domenic, thank you so much for this!! This is the right answer.

I switched the marked solution post accordingly.

@Coyotex3 - in the future, please mark the post as the solution that answered your question instead of your feedback post as it will help future readers.

 

[Coyotex3]

I switched the marked solution post accordingly.

@Coyotex3 - in the future, please mark the post as the solution that answered your question instead of your feedback post as it will help future readers.

My apologies. I could have swore I marked it as a solution.

@Domenic Sorry about that.

 

[smozgur]

My apologies. I could have swore I marked it as a solution.

No problem at all. I just wanted to let you know that I switched the solution post. Thanks.