Multiple ranges to one pdf

jt1954

New Member
Joined
Aug 18, 2011
Messages
8
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:

Code:
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)?
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
this creates a new temp sheet, copies Ranges to it,
creates PDF then deletes temp sheet.
Code:
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
 
Last edited:
Upvote 0
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.:)
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top