Print Macro as one PDF file

[theanchorman]

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?

 

[theanchorman]

Thanks Yongle for your responses! I will try to determine which method will work best and give these a try.

 

[theanchorman]

COMBINING RANGES INTO A SINGLE SHEET WHICH IS PRINTED TO SINGLE PDF
- method appropriate for ranges sharing common header rows etc

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

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​	Header1	Header2	Header3
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​	a01	b01	c01		smtestp1A
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​	a02	b02	c02
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​	a03	b03	c03
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​	a04	b04	c04
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​	a05	b05	c05
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​	a06	b06	c06
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]13[/COLOR]​	a11	b11	c11		smtestp1B
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]14[/COLOR]​	a12	b12	c12
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]15[/COLOR]​	a13	b13	c13
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]16[/COLOR]​	a14	b14	c14
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]17[/COLOR]​	a15	b15	c15
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]18[/COLOR]​	a16	b16	c16
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]19[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]20[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]21[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]22[/COLOR]​	a21	b22	c22		smtestp1C
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]23[/COLOR]​	a22	b23	c23
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]24[/COLOR]​	a23	b24	c24
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]25[/COLOR]​	a24	b25	c25
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]26[/COLOR]​	a25	b26	c26
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]27[/COLOR]​	a26	b27	c27
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]28[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]29[/COLOR]​

<tbody>
</tbody>

Sheet: Data

<tbody>
</tbody>

How can I rewrite the code above listed as "ws.PageSetup.PrintTitleRows = "$1:$1"" with the specific ranges such as my previous code had as shown below? Instead of the first row, I want it to use my specific selected ranges as rows and columns.

With ActiveSheet.PageSetup
.PrintTitleRows = "inbrdrow"
.PrintTitleColumns = "inbrdcol"
End With