Saving Multiple Ranges on two different sheets to PDF using VBA

Question

I need to get both of these sheets and ranges to be combined into ONE PDF. I have tried all of the macros I can find and none of them work. Here is the Macro I'm working with, which all works except for the ranges being combined in one Doc

Private Sub SaveLHForms()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

FormName = Sheets("SETUP").Range("B2").Value & " " & ActiveSheet.Range("S1") & ".pdf"

    ChDir DesktopAddress
    Sheets("Lienholder Docs").Range("A45:I151").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        FormName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
       False
    Sheets("Settlement Letters").Range("A47:I92").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        FormName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        False

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

1. Set PageSetup.PrintArea in each two sheet. And then export to pdf multi sheet. — Dy.Lee, Nov 04 '17 at 02:38
2. Sheets(Array("Lienholder Docs", "Settlement Letters")).ExportAsFixedFormat — Dy.Lee, Nov 04 '17 at 02:40

Gary's Student · Answer 1 · 2017-11-03T19:13:49.123

This may work:

Sub Macro1()

   Sheets("Lienholder Docs").Activate
   ActiveSheet.Range("A45:I151").Select
   Sheets("Settlement Letters").Activate
   ActiveSheet.Range("A47:I92").Select

   Sheets(Array("Lienholder Docs", "Settlement Letters")).Select

   Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        FormName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        False
End Sub

Based on:

Excel VBA to Export Selected Sheets to PDF

EDIT#1:

This version should un-do any grouping:

Sub Macro2()
    Dim s As Worksheet
    Set s = ActiveSheet
    FormName = "C:\TestFolder\xxx.pdf"

    Sheets("Lienholder Docs").Activate
    ActiveSheet.Range("A45:I151").Select
    Sheets("Settlement Letters").Activate
    ActiveSheet.Range("A47:I92").Select

   Sheets(Array("Lienholder Docs", "Settlement Letters")).Select

   Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        FormName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        False

    s.Activate
End Sub

the only problem I see is that what it's doing when i run the macro is it's grouping the two sheets in the excel doc. — Tim Helsley Jr., Nov 03 '17 at 19:03
I copied It exactly except I had to change the destination to desktop. It did run the macro - however the range's we are referencing equate to 3 pages worth of data on "Lienholder Docs" and then one page worth of data for "Settlement Letters". It's disregarding the exact range value and just grabbing bits of each page. And it's still changing the excel to [Grouped] which causes other macros not to run. — Tim Helsley Jr., Nov 03 '17 at 20:51

score 2 · Answer 2 · answered Nov 04 '17 at 14:11

First, you need to create a pdf document after setting the print area in the page settings of each sheet.

Sub test()
    Dim path As String
    Dim myArr As Variant, a As Variant
    Dim rngArr As Variant
    Dim Ws As Worksheet
    Dim formName As String
    Dim i As Integer

    formName = Sheets("SETUP").Range("B2").Value & " " & ActiveSheet.Range("S1") & ".pdf"

    myArr = Array("Lienholder Docs", "Settlement Letters") '<~~ Sheet name
    rngArr = Array("A45:I151", "A47:I92") '<~~ print area address

    For i = 0 To UBound(myArr)
        Set Ws = Sheets(myArr(i))
        With Ws
            .PageSetup.PrintArea = .Range(rngArr(i)).Address
        End With
    Next a
    Sheets(myArr).Select

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        formName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        False


End Sub

Saving Multiple Ranges on two different sheets to PDF using VBA

2 Answers2