Print Macro as one PDF file

theanchorman

New Member
Joined
Aug 24, 2012
Messages
31
Hello,

I have a print macro in excel that prints multiple ranges in my file (roughly 100 pages). I tried to print as a PDF file, however it tries to save each page as a pdf and asks me to name each page. Is there a way for me to save/print all 100 pages a one batch file pdf? I don't believe Microsoft has this feature and it may need to be a macro vba code. Any suggestions?
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
please post your current print macro
- click on # icon above post window first and then ...
[ CODE ] paste your code inside the tags [ /CODE ]

thanks
 
Upvote 0
Code:
    Format_for_Printing
   
    With ActiveSheet.PageSetup
        .PrintTitleRows = "smbrdrow"
        .PrintTitleColumns = "smbrdcol"
    End With
   
    Application.Goto Reference:="smtestp1A"
    Selection.PrintOut
   
    Application.Goto Reference:="smtestp1B"
    Selection.PrintOut
   
    Application.Goto Reference:="smtestp1C"
    Selection.PrintOut
   
    Application.Goto Reference:="smtestp1D"
    Selection.PrintOut
                      
    Application.Goto Reference:="smtestp2A"
    Selection.PrintOut
   
    Application.Goto Reference:="smtestp2B"
    Selection.PrintOut
   
    Application.Goto Reference:="smtestp2C"
    Selection.PrintOut
   
    Application.Goto Reference:="smtestp2D"
    Selection.PrintOut
   
    Sheets("Notes").Select
   
    Format_for_Printing_Notes
   
    With ActiveSheet.PageSetup
        .PrintTitleRows = "smbrdrowftnt"
        '.PrintTitleColumns = "smbrdcol"
    End With
   
    Application.Goto Reference:="smftntp1a"
    Selection.PrintOut
   
    Sheets("MODEL").Select
   
    Format_for_Printing
   
    With ActiveSheet.PageSetup
        .PrintTitleRows = "cwbrdrow"
        .PrintTitleColumns = "cwbrdcol"
    End With
    
    Application.Goto Reference:="cwtestp1a"
    Selection.PrintOut
   
    Application.Goto Reference:="cwtestp1b"
    Selection.PrintOut
   
    Application.Goto Reference:="cwtestp1c"
    Selection.PrintOut
   
    Application.Goto Reference:="cwtestp1d"
    Selection.PrintOut
   
    Sheets("Notes").Select
   
    Format_for_Printing_Notes
   
    With ActiveSheet.PageSetup
        .PrintTitleRows = "cwbrdrowftnt"
        '.PrintTitleColumns = "cwbrdcol"
    End With
   
    Application.Goto Reference:="cwftntp1a"
    Selection.PrintOut
   
    Sheets("MODEL").Select
   
    Format_for_Printing
        
    With ActiveSheet.PageSetup
        .PrintTitleRows = "inbrdrow"
        .PrintTitleColumns = "inbrdcol"
    End With
   
    Application.Goto Reference:="intestp1a"
    Selection.PrintOut
   
    Application.Goto Reference:="intestp1b"
    Selection.PrintOut
   
    Application.Goto Reference:="intestp1c"
    Selection.PrintOut
   
    Application.Goto Reference:="intestp1d"
    Selection.PrintOut
       
    Application.Goto Reference:="intestp2a"
    Selection.PrintOut
   
    Application.Goto Reference:="intestp2b"
    Selection.PrintOut
   
    Application.Goto Reference:="intestp2c"
    Selection.PrintOut
   
    Application.Goto Reference:="intestp2d"
    Selection.PrintOut
   
    Sheets("Notes").Select
   
    Format_for_Printing_Notes
   
    With ActiveSheet.PageSetup
        .PrintTitleRows = "inbrdrowftnt"
        '.PrintTitleColumns = "inbrdcol"
    End With
   
    Application.Goto Reference:="inftntp1a"
    Selection.PrintOut
   
    Sheets("MODEL").Select
   
    Format_for_Printing
   
    With ActiveSheet.PageSetup
        .PrintTitleRows = "gpbrdrow"
        .PrintTitleColumns = "gpbrdcol"
    End With
   
     Application.Goto Reference:="gptestp1a"
    Selection.PrintOut
   
    Application.Goto Reference:="gptestp1b"
    Selection.PrintOut
   
    Application.Goto Reference:="gptestp1c"
    Selection.PrintOut
   
    Application.Goto Reference:="gptestp1d"
    Selection.PrintOut
      
    Application.Goto Reference:="gptestp2a"
    Selection.PrintOut
   
    Application.Goto Reference:="gptestp2b"
    Selection.PrintOut
   
    Application.Goto Reference:="gptestp2c"
    Selection.PrintOut
   
    Application.Goto Reference:="gptestp2d"
    Selection.PrintOut
       
    Application.Goto Reference:="gptestp3a"
    Selection.PrintOut
   
    Application.Goto Reference:="gptestp3b"
    Selection.PrintOut
   
    Application.Goto Reference:="gptestp3c"
    Selection.PrintOut
   
    Application.Goto Reference:="gptestp3d"
    Selection.PrintOut
      
    Application.Goto Reference:="gptestp4a"
    Selection.PrintOut
   
    Application.Goto Reference:="gptestp4b"
    Selection.PrintOut
   
    Application.Goto Reference:="gptestp4c"
    Selection.PrintOut
   
    Application.Goto Reference:="gptestp4d"
    Selection.PrintOut
   
    Application.Goto Reference:="gptestp5a"
    Selection.PrintOut
   
    Application.Goto Reference:="gptestp5b"
    Selection.PrintOut
   
    Application.Goto Reference:="gptestp5c"
    Selection.PrintOut
   
    Application.Goto Reference:="gptestp5d"
    Selection.PrintOut
   
    Sheets("Notes").Select
   
    Format_for_Printing_Notes
   
    With ActiveSheet.PageSetup
        .PrintTitleRows = "gpbrdrowftnt"
        '.PrintTitleColumns = "gpbrdcol"
    End With
   
    Application.Goto Reference:="gpftntp1a"
    Selection.PrintOut
   
    Application.Goto Reference:="gpftntp1b"
    Selection.PrintOut
   
    Application.Goto Reference:="gpftntp1c"
    Selection.PrintOut
   
    Sheets("MODEL").Select
   
    Format_for_Printing
   
    With ActiveSheet.PageSetup
        .PrintTitleRows = "adbrdrow"
        .PrintTitleColumns = "adbrdcol"
    End With
   
    Application.Goto Reference:="adtestp1a"
    Selection.PrintOut
   
    Application.Goto Reference:="adtestp1b"
    Selection.PrintOut
   
    Application.Goto Reference:="adtestp1c"
    Selection.PrintOut
      
    Application.Goto Reference:="adtestp1d"
    Selection.PrintOut
   
    Application.Goto Reference:="adtestp2a"
    Selection.PrintOut
   
    Application.Goto Reference:="adtestp2b"
    Selection.PrintOut
   
    Application.Goto Reference:="adtestp2c"
    Selection.PrintOut
   
    Application.Goto Reference:="adtestp2d"
    Selection.PrintOut
   
    Sheets("Notes").Select
   
    Format_for_Printing_Notes
   
    With ActiveSheet.PageSetup
        .PrintTitleRows = "adbrdrowftnt"
        '.PrintTitleColumns = "adbrdcol"
    End With
   
    Application.Goto Reference:="adftntp1a"
    Selection.PrintOut
   
    Application.Goto Reference:="adftntp1b"
    Selection.PrintOut
   
    Sheets("MODEL").Select
   
    Format_for_Printing
   
    With ActiveSheet.PageSetup
        .PrintTitleRows = "msbrdrow"
        .PrintTitleColumns = "msbrdcol"
    End With
   
    Application.Goto Reference:="mstestp1a"
    Selection.PrintOut
   
    Application.Goto Reference:="mstestp1b"
    Selection.PrintOut
   
    Application.Goto Reference:="mstestp1c"
    Selection.PrintOut
   
    Application.Goto Reference:="mstestp1d"
    Selection.PrintOut
   
    Sheets("Notes").Select
   
    Format_for_Printing_Notes
   
    With ActiveSheet.PageSetup
        .PrintTitleRows = "msbrdrowftnt"
        '.PrintTitleColumns = "msbrdcol"
    End With
   
    Application.Goto Reference:="msftntp1a"
    Selection.PrintOut
   
    Sheets("MODEL").Select
   
    Format_for_Printing
   
    With ActiveSheet.PageSetup
        .PrintTitleRows = "orbrdrow"
        .PrintTitleColumns = "orbrdcol"
    End With
   
    Application.Goto Reference:="ortestp1a"
    Selection.PrintOut
   
    Application.Goto Reference:="ortestp1b"
    Selection.PrintOut
   
    Application.Goto Reference:="ortestp1c"
    Selection.PrintOut
   
    Application.Goto Reference:="ortestp1d"
    Selection.PrintOut
  
    Application.Goto Reference:="ortestp2a"
    Selection.PrintOut
   
    Application.Goto Reference:="ortestp2b"
    Selection.PrintOut
   
    Application.Goto Reference:="ortestp2c"
    Selection.PrintOut
   
    Application.Goto Reference:="ortestp2d"
    Selection.PrintOut
   
    Application.Goto Reference:="ortestp3a"
    Selection.PrintOut
   
    Application.Goto Reference:="ortestp3b"
    Selection.PrintOut
   
    Application.Goto Reference:="ortestp3c"
    Selection.PrintOut
   
    Application.Goto Reference:="ortestp3d"
    Selection.PrintOut
   
    Sheets("Notes").Select
   
    Format_for_Printing_Notes
   
    With ActiveSheet.PageSetup
        .PrintTitleRows = "orbrdrowftnt"
        '.PrintTitleColumns = "orbrdcol"
    End With
   
    Application.Goto Reference:="orftntp1a"
    Selection.PrintOut
   
    Application.Goto Reference:="orftntp1b"
    Selection.PrintOut
   
    Application.Goto Reference:="orftntp1c"
    Selection.PrintOut
   
    Sheets("MODEL").Select
   
    Format_for_Printing
   
    With ActiveSheet.PageSetup
        .PrintTitleRows = "rvbrdrow"
        .PrintTitleColumns = "rvbrdcol"
    End With
   
    Application.Goto Reference:="rvtestp1a"
    Selection.PrintOut
   
    Application.Goto Reference:="rvtestp1b"
    Selection.PrintOut
   
    Application.Goto Reference:="rvtestp1c"
    Selection.PrintOut
   
    Application.Goto Reference:="rvtestp1d"
    Selection.PrintOut
   
    Application.Goto Reference:="rvtestp2a"
    Selection.PrintOut
   
    Application.Goto Reference:="rvtestp2b"
    Selection.PrintOut
   
    Application.Goto Reference:="rvtestp2c"
    Selection.PrintOut
   
    Application.Goto Reference:="rvtestp2d"
    Selection.PrintOut
   
    Application.Goto Reference:="rvtestp3a"
    Selection.PrintOut
   
    Application.Goto Reference:="rvtestp3b"
    Selection.PrintOut
   
    Application.Goto Reference:="rvtestp3c"
    Selection.PrintOut
   
    Application.Goto Reference:="rvtestp3d"
    Selection.PrintOut
   
    Sheets("Notes").Select
   
    Format_for_Printing_Notes
   
    With ActiveSheet.PageSetup
        .PrintTitleRows = "rvbrdrowftnt"
        '.PrintTitleColumns = "rvbrdcol"
    End With
   
    Application.Goto Reference:="rvftntp1a"
    Selection.PrintOut
   
    Application.Goto Reference:="rvftntp1b"
    Selection.PrintOut
   
    Sheets("MODEL").Select
   
    Format_for_Printing
   
    With ActiveSheet.PageSetup
        .PrintTitleRows = "ombrdrow"
        .PrintTitleColumns = "ombrdcol"
    End With
   
    Application.Goto Reference:="omtestp1a"
    Selection.PrintOut
   
    Application.Goto Reference:="omtestp1b"
    Selection.PrintOut
   
    Application.Goto Reference:="omtestp1c"
    Selection.PrintOut
   
    Application.Goto Reference:="omtestp1d"
    Selection.PrintOut
   
    Application.Goto Reference:="omtestp2a"
    Selection.PrintOut
   
    Application.Goto Reference:="omtestp2b"
    Selection.PrintOut
   
    Application.Goto Reference:="omtestp2c"
    Selection.PrintOut
   
    Application.Goto Reference:="omtestp2d"
    Selection.PrintOut
   
    Application.Goto Reference:="omtestp3a"
    Selection.PrintOut
   
    Application.Goto Reference:="omtestp3b"
    Selection.PrintOut
   
    Application.Goto Reference:="omtestp3c"
    Selection.PrintOut
   
    Application.Goto Reference:="omtestp3d"
    Selection.PrintOut
   
    Application.Goto Reference:="omtestp4a"
    Selection.PrintOut
   
    Application.Goto Reference:="omtestp4b"
    Selection.PrintOut
   
    Application.Goto Reference:="omtestp4c"
    Selection.PrintOut
   
    Application.Goto Reference:="omtestp4d"
    Selection.PrintOut
   
    Sheets("Notes").Select
   
    Format_for_Printing_Notes
   
    With ActiveSheet.PageSetup
        .PrintTitleRows = "ombrdrowftnt"
        '.PrintTitleColumns = "ombrdcol"
    End With
   
    Application.Goto Reference:="omftntp1a"
    Selection.PrintOut
   
    Application.Goto Reference:="omftntp1b"
    Selection.PrintOut
   
    Application.Goto Reference:="omftntp1c"
    Selection.PrintOut
   
    Sheets("MODEL").Select
   
    Format_for_Printing
   
    With ActiveSheet.PageSetup
        .PrintTitleRows = "debrdrow"
        .PrintTitleColumns = "debrdcol"
    End With
   
    Application.Goto Reference:="detestp1a"
    Selection.PrintOut
   
    Application.Goto Reference:="detestp1b"
    Selection.PrintOut
   
    Application.Goto Reference:="detestp1c"
    Selection.PrintOut
   
    Application.Goto Reference:="detestp1d"
    Selection.PrintOut
   
    Application.Goto Reference:="detestp2a"
    Selection.PrintOut
   
    Application.Goto Reference:="detestp2b"
    Selection.PrintOut
   
    Application.Goto Reference:="detestp2c"
    Selection.PrintOut
   
    Application.Goto Reference:="detestp2d"
    Selection.PrintOut
   
    Sheets("Notes").Select
   
    Format_for_Printing_Notes
   
    With ActiveSheet.PageSetup
        .PrintTitleRows = "debrdrowftnt"
        '.PrintTitleColumns = "debrdcol"
    End With
   
    Application.Goto Reference:="deftntp1a"
    Selection.PrintOut
   
    Application.Goto Reference:="deftntp1b"
    Selection.PrintOut
   
    Sheets("MODEL").Select
   
    Format_for_Printing
   
    With ActiveSheet.PageSetup
        .PrintTitleRows = "txbrdrow"
        .PrintTitleColumns = "txbrdcol"
    End With
   
    Application.Goto Reference:="txtestp1a"
    Selection.PrintOut
   
    Application.Goto Reference:="txtestp1b"
    Selection.PrintOut
   
    Application.Goto Reference:="txtestp1c"
    Selection.PrintOut
   
    Application.Goto Reference:="txtestp1d"
    Selection.PrintOut
   
    Sheets("Notes").Select
   
    Format_for_Printing_Notes
   
    With ActiveSheet.PageSetup
        .PrintTitleRows = "txbrdrowftnt"
        '.PrintTitleColumns = "txbrdcol"
    End With
   
    Application.Goto Reference:="txftntp1a"
    Selection.PrintOut
   
    Sheets("MODEL").Select
   
    Format_for_Printing
   
    With ActiveSheet.PageSetup
        .PrintTitleRows = "albrdrow"
        .PrintTitleColumns = "albrdcol"
    End With
   
    Application.Goto Reference:="altestp1a"
    Selection.PrintOut
   
    Application.Goto Reference:="altestp1b"
    Selection.PrintOut
   
    Application.Goto Reference:="altestp1c"
    Selection.PrintOut
   
    Application.Goto Reference:="altestp1d"
    Selection.PrintOut
   
    Sheets("Notes").Select
   
    Format_for_Printing_Notes
   
    With ActiveSheet.PageSetup
        .PrintTitleRows = "albrdrowftnt"
        '.PrintTitleColumns = "albrdcol"
    End With
   
    Application.Goto Reference:="alftntp1a"
    Selection.PrintOut
   
    Sheets("MODEL").Select
   
    Range("MODEL!A1").Select
    
     End Sub
 
Last edited:
Upvote 0
1. One solution is to create 100 PDF files (autonamed by VBA from 00 to 99) and then combine them sequentially using 3rd party software
- various programs available eg Adobe Acrobat (not Adobe Acrobat Reader)

2. If the layout of each "page" is very similar consider using VBA to combine all ranges into ONE sheet which could then be saved as a multipage PDF

3. Another option is to build a Word document from your ranges and save that as a multipage PDF using VBA - complexity depends on what your workbook contains!!

4. Or auto-build a PowerPoint presentation fromt he ranges and save as a multipage PDF
 
Upvote 0
I like option 1. What would be the VBA code for that? Would I have to write the below code (for example) for each range name?

Sub SaveAsA1()
ThisFile = Range("A1").Value
ActiveWorkbook.SaveAs Filename:=ThisFile
End Sub
 
Last edited:
Upvote 0
this should wotk for you. Found it on google.

Code:
Sub PrintAllSheetsToPDF()'SUBROUTINE: PrintAllSheetsToPDF
'DEVELOPER: Ryan Wells
'DESCRIPTION: Combine all your worksheets into one PDF
Dim strSheets() As String
Dim strfile As String
Dim sh As Worksheet
Dim icount As Integer
Dim myfile As Variant
'Save Chart Sheet names to an Array
For Each sh In ActiveWorkbook.Worksheets
If sh.Visible = xlSheetVisible Then
ReDim Preserve strSheets(icount)
strSheets(icount) = sh.Name
icount = icount + 1
End If
Next sh
If icount = 0 Then 'No charts found. Punch error
MsgBox "A PDF cannot be created because no sheets were found.", , "No Sheets Found"
Exit Sub
End If
'Prompt for save location
strfile = "Sheets" & "_" _
& Format(Now(), "yyyymmdd_hhmmss") _
& ".pdf"
strfile = ThisWorkbook.Path & "\" & strfile
myfile = Application.GetSaveAsFilename _
(InitialFileName:=strfile, _
FileFilter:="PDF Files (*.pdf), *.pdf", _
Title:="Select Folder and File Name to Save as PDF")
If myfile <> "False" Then 'save as PDF
ThisWorkbook.Sheets(strSheets).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
myfile, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
Else
MsgBox "No File Selected. PDF will not be saved", vbOKOnly, "No File Selected"
End If
End Sub
 
Upvote 0
I believe I have used the code before. The Description at the top says "Combine all your worksheets into one PDF." All of my data is on one worksheet, but its printing many ranges. I need something that would combine all of the ranges into one PDF.
 
Upvote 0
I like option 1. What would be the VBA code for that? Would I have to write the below code (for example) for each range name?

Print each named ranges into separate PDFs like this
- amend path constant and run

Code:
Sub Test()
[COLOR=#006400][I]'variables[/I][/COLOR]
    Const path = "[COLOR=#ff0000]C:\Test\pdf[/COLOR][COLOR=#ff0000]\[/COLOR]"             [I]'include trailing[COLOR=#ff0000] \[/COLOR][/I]
    Dim nm As Variant, ws As Worksheet
    Set ws = ActiveSheet

[COLOR=#006400]'loop named ranges[/COLOR]
    For Each nm In Array("smtestp1A", "smtestp1B", "smtestp1C")
        ws.Range(nm).ExportAsFixedFormat Type:=xlTypePDF, Filename:=path & nm & ".pdf"
    Next nm
End Sub


Adapting your original code
- hopefully you can loop (as above) for each of the ranges that share the same title rows and columns

- otherwise could try replacing each pair of lines
Code:
    Application.Goto Reference:="[COLOR=#ff0000]smtestp1A[/COLOR]"
    Selection.PrintOut
with
Code:
   ActiveSheet.Range("[COLOR=#ff0000]smtestp1A[/COLOR]").ExportAsFixedFormat Type:=xlTypePDF, Filename:=path & [COLOR=#ff0000]smtestp1A[/COLOR] & ".pdf"

I would amend the original print code first so that the named ranges are looped wherever possible BEFORE adapting to do the same for PDF export
 
Last edited:
Upvote 0
The max edit time cut me off

previous post cont ...

I would amend the original print code first so that the named ranges are looped wherever possible BEFORE adapting to do the same for PDF export
something like this for common ranges
Code:
    Dim nm As Variant
    For Each nm In Array("smtestp1A", "smtestp1B", "smtestp1C")
        Application.Goto Reference:=nm
        Selection.PrintOut
    Next nm

or simplify and avoid selecting ...
Code:
    Dim nm As Variant
    For Each nm In Array("smtestp1A", "smtestp1B", "smtestp1C")
        ActiveSheet.Range(nm).PrintOut
    Next nm
 
Last edited:
Upvote 0
COMBINING RANGES INTO A SINGLE SHEET WHICH IS PRINTED TO SINGLE PDF
- method appropriate for ranges sharing common header rows etc

Code:
Sub Test()
'variables
    Const path = "C:\Test\PDF[COLOR=#ff0000]\[/COLOR]"     'end with [COLOR=#ff0000]\[/COLOR]
    Dim nm  As Variant
    Dim r   As Long
    Dim sh  As Worksheet:       Set sh = ActiveSheet
'create combo sheet with headers
    Dim ws  As Worksheet:       Set ws = Sheets.Add
    sh.Rows(1).Copy ws.Cells(1)
    ws.PageSetup.PrintTitleRows = "$1:$1"
'loop named ranges
    For Each nm In Array("smtestp1A", "smtestp1B", "smtestp1C")
        r = ws.UsedRange.Rows.Count + 1
        If r <> 2 Then
            ws.Rows(r).PageBreak = xlPageBreakManual
            r = r + 1
        End If
        sh.Range(nm).Copy ws.Cells(r, 1)
    Next nm
'save to PDF
    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=path & "[COLOR=#ff0000]YourFileName[/COLOR]" & ".pdf"
End Sub


With worksheet laid out as below, and named ranges specified as per coloured cells, the above code created a single PDF with one page for each named range

Excel 2016 (Windows) 32 bit
A
B
C
D
E
1
Header1Header2Header3
2
3
4
a01b01c01smtestp1A
5
a02b02c02
6
a03b03c03
7
a04b04c04
8
a05b05c05
9
a06b06c06
10
11
12
13
a11b11c11smtestp1B
14
a12b12c12
15
a13b13c13
16
a14b14c14
17
a15b15c15
18
a16b16c16
19
20
21
22
a21b22c22smtestp1C
23
a22b23c23
24
a23b24c24
25
a24b25c25
26
a25b26c26
27
a26b27c27
28
29
Sheet: Data
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

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