Multiple ranges to one pdf

[jt1954]

Very much new to VBA. Have been reading posts on here to try and learn and have utilised some of that expertise below.

I am trying to print multiple excel range names to one PDF.

This works OK for me when the range names (first, second and third) are all on one sheet (Sheet1)

The code so far is given here:

Sub pdfrangecombined()
Dim tempPDFFileName As String
Dim tempPSFileName As String
Dim tempPDFRawFileName As String
Dim tempLogFileName As String

tempPDFRawFileName = "c:\pdftest\trial"

'Define the postscript and .pdf file names.

tempPSFileName = tempPDFRawFileName & ".ps"
tempPDFFileName = tempPDFRawFileName & ".pdf"
tempLogFileName = tempPDFRawFileName & ".log"

Application.Range("first, second, third").Select

Selection.PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", printtofile:=True, Collate:=True, prtofilename:=tempPSFileName
'Create PDF File

Dim myPDFDist As New PdfDistiller
myPDFDist.FileToPDF tempPSFileName, tempPDFFileName, tempShowWindow

'Next
'Delete PS File

Kill tempPSFileName
Kill tempLogFileName

End Sub

How can this be modified to allow for including additional range names (fourth, fifth and sixth) from a second sheet (Sheet2)?

 

[Warship]

this creates a new temp sheet, copies Ranges to it,
creates PDF then deletes temp sheet.

Sub CreateMultiRangeOnePagePDF()
  Dim RangeArray() As Variant
  Dim x As Long, LR As Long
  Const RngPad As Long = 1 'set to number of rows between ranges
  RangeArray = Array("Range1", "Range2", "Range3")
  Application.ScreenUpdating = False
  Sheets.Add After:=Sheets(Sheets.Count)
  For x = 0 To UBound(RangeArray)
    LR = Sheets(Sheets.Count).Cells(Rows.Count, 1).End(xlUp).Row
    If LR <> 1 Then LR = LR + 1 + RngPad
    Range(RangeArray(x)).Copy
    Sheets(Sheets.Count).Cells(LR, 1).PasteSpecial Paste:=xlPasteValues
    Selection.PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
  Next x
  Sheets(Sheets.Count).ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:="C:\Test.pdf", _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False
  Application.DisplayAlerts = False
  Sheets(Sheets.Count).Delete
  Application.DisplayAlerts = True
End Sub

 

[jt1954]

Thank you - will try out over the week-end and let you know how I get on.

 

[jt1954]

Sir, you are a gentleman and a scholar! This works a treat! Thank you so much. It confirms what I already knew - I know nothing! But I sure as hell am going to start trying to learn VBA beyond the simple "record macro" stage.