How to save specific multiple worksheets to a pdf file programmatically
This article demonstrates macros that save worksheets to a single pdf file.
What's on this webpage
1. Export all worksheets in workbook to PDF programmatically
The following macro saves all worksheets in the active workbook to a single pdf file.
'Name macro
Sub ExportWbtoPdf()
'Select all worksheets in active workbook
For Each WS In ActiveWorkbook.Worksheets
Worksheets(WS.Name).Select False
Next WS
'Ask for a directory to save the pdf file in
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
myfolder = .SelectedItems(1) & "\"
End With
'Ask for a save file name for the pdf file
myfile = InputBox("Enter file name", "Save as..")
'Save all worksheets in workbook to pdf file
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
myfolder & myfile _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
End Sub
2. How to save specific multiple worksheets to a pdf file programmatically
2.1 How to use the macro?
Make sure you select the worksheets you want to save as a single pdf file before you run the macro. To select multiple worksheets press and hold CTRL and then press with left mouse button on with the mouse on the worksheet tabs located at the bottom of your Excel screen.
This happens when you start the macro
- Confirm selected sheets by press with left mouse button oning "Yes".
- A dialog box appears, choose a save folder. Press with left mouse button on OK.
- A new dialog box appears. Enter a file name.
- Press with left mouse button on OK button.
- A pdf file is created and opens automatically.
2.2 VBA code
Sub SaveSelectedSheetsToPDF()
Dim str As String, myfolder As String, myfile As String
str = "Do you want to save these sheets to a single pdf file?" & Chr(10)
For Each sht In ActiveWindow.SelectedSheets
str = str & sht.Name & Chr(10)
Next sht
answer = MsgBox(str, vbYesNo, "Continue with save?")
If answer = vbNo Then Exit Sub
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
myfolder = .SelectedItems(1) & "\"
End With
myfile = InputBox("Enter filename", "Save as..")
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
myfolder & myfile _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
End Sub
3. Where do I put the code in my workbook?
- Start the Visual Basic Editor (Alt+F11).
- Press with left mouse button on "Insert" on the top menu, see image above.
- Press with left mouse button on "Module".
- Paste code to window.
- Return to Excel.
Explaining the macro
A macro starts with Sub and then the macro name
Sub SaveSelectedSheetsToPDF()
Declaring variables
Variables str, myfolder and myfile are declared data type String.
Read more about Defining data types.
Dim str As String, myfolder As String, myfile As String
Concatenate text stringsÂ
The selected sheet names are concatenated with a question, Chr(10) is a carriage return.
str = "Do you want to save these sheets to a single pdf file?" & Chr(10) For Each sht In ActiveWindow.SelectedSheets str = str & sht.Name & Chr(10) Next sht
Message box Â
The message box lets you confirm you have selected the correct sheets. If the answer is no the macro ends.
answer = MsgBox(str, vbYesNo, "Continue with save?") If answer = vbNo Then Exit Sub
Choose a directory
The directory is saved in the string variable myfolder.
With Application.FileDialog(msoFileDialogFolderPicker) .Show myfolder = .SelectedItems(1) & "\" End With
Enter a file name
The file name is saved in the string variable myfile.
myfile = InputBox("Enter file name", "Save as..")
Create pdf
The selected sheets are saved as a pdf file with the name myfile and path myfolder.
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ myfolder & myfile _ , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _ :=False, OpenAfterPublish:=True
End a macro
A macro ends with this statement.
End Sub
Assign macro to a button for quick access
- Go to tab "Developer" on the ribbon
- Press with left mouse button on "Insert" button
- Press with left mouse button on "Button (Form Control)
- Press with left mouse button on and drag on sheet to build a button
- Assign macro SaveSelectedSheetsToPDF
- Press with left mouse button on OK
Save the macro in your personal macro workbook
If you save the macro in a personal macro workbook, you can access that macro no matter what workbook you have open.
Read this:Â Copy your macros to a Personal Macro Workbook
Macro category
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
Excel categories
13 Responses to “How to save specific multiple worksheets to a pdf file programmatically”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Hi Oscar
First of all thanks for your beautiful macro to copy an Excel file to PDF. If you want to copy a wide horizontal field to PDF, it can cause a split in the PDF: you get divided the text, graphics and / or object on two pages). That's not what you want!
To make a perfect horizontal layout, I recommend to define first in the worksheet: Print Range, Page Layout (on ribbon). Create with File / Print Options other necessary instructions, for example, choose settings like "print to fit", borders etc.etc. The actual print layout is displayed on the right side.
Instead of a normal print, you can now activate the Macro. These simple adjustments don't give a distribution on two pages, but an appropriate horizontal PDF format. So, it's a contribution to your readers.
Best regards,
Bert van Zandbergen
Bert van Zandbergen,
thank you for commenting.
Hi, Oscar
your code works great but it only prints excel sheets that have been individually selected before running the macro. is there a way to get the code to automatically select all unhidden sheets in excel and then print to pdf.
The reason why i say unhidden sheets is, because i have a large userform that based on user selections hides unneeded excel sheets. so i would only need to print the relevant sheets, which would be the unhidden sheets.
thanks for your time,
Tyler Estrada
Hi Oscar
thanks for the great effort
one question
if I want to export each sheet as a separate pdf file, what should be changed in the code?
Thanks
Mohamed
Hi,
I just found the error: I also opened the xlsm file, had a look at the code there, and found that it differs from the code at the top of this article (I copied the code from there first).
The difference was in the "amp;amp;amp;amp" portion.
After removing these, the macro is running smooth now.
Cheers
Luc
Oscar - Many, many thks for this, it was an excellent post, and a great solution for what I was looking for. One question, I was wondering how to do this based on a validation list. For example, lets say I have 10 sheets. I have a drop down list say in A1 of Sheet1, which the workbook menu. The drop down list contains - say two reports - Short Report and Long Report.
If I press with left mouse button on Short Report, it will print pages - 2,3, and 5. If I select Long Report, it will print pages 2, 6,7,8,9, and 10.
I would like to have the same option for a single PDF file. Thus, if I select Short Report, pages 2, 3 and 5 will be saved in a single PDF file. Any assistance on this would be greatly appreciated. cheers
Joe
Dear Sir,
when i run the macro its showing "run time error 5".invalid procedure or call argument.
please help us to solved the issues.
Thanks
Manoj
MANOJ,
WordPress has added characters that shouldn't be in the code, I have tried to remove them. I recommend you try out the attached file.
Dear Sir,
You have recommended to get the attached file but that is not reflecting,is it possible send me through mail([email protected])
Regards
Manoj
Dear Sir,
Please go through the below code and help me to solve the issues that when i run the macro its showing run time error 5".invalid procedure or call argument.
Sub saveinvoice()
Sheet2.Range("a1:l57").ExportAsFixedFormat xlTypePDF, Filename:=" C:\Users\USER\Desktop\inventory\" & Sheet2.Range("j11").Value, openafterpublish:=True
Thanks
Manoj
End Sub
How can you choose which sheet is printed first and which one is printed next?
hussain assani,
You need to rearrange the worksheet tabs to be able to print them in a given order.
To sort the worksheets in a given order you can press with the left mouse button and hold on a worksheet tab and drag to the position you want, see this guide:
https://www.extendoffice.com/documents/excel/3410-excel-change-orders-of-tabs.html#a1
Hi Oscar,
Thanks for your excellent work. The save selected sheets macro is precisely what I needed. However it runs just fine in O365 for Windows but gives me an error on O365 for Mac. Getting a 'Run-time error 91' 'Object variable or With block variable not set'. Tried a few things with the .Show and myfolder variable in the With block but no luck.
Any clues on what might be the problem?
Thanks in advance