This tutorial looks at how you can create a macro in Excel to export all sheets to PDF. Each sheet of the workbook will be saved into the same folder. The name of the worksheet will be used as the filename of the PDF.
By creating a macro this seemingly repetitive task can be performed at the click of a button.
Although all sheets are used in this example. The macro can be adapted to include all except the first sheet, or some other variation.
The code below can be copied into a VBA module and adapted to your needs. An explanation for the main parts of the code can be found below.
Sub ExportAsPDF() Dim FolderPath as string Dim I as Integer FolderPath = "C:\Users\Computergaga\Desktop\PDFs" MkDir FolderPath For i = 1 To Worksheets.Count Worksheets(i).ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & "\" & _ Worksheets(i).Name, openafterpublish:=False Next MsgBox "All PDF's have been successfully exported." End Sub
The MkDir function has been used to create a directory (folder) on the Desktop. This folder is then used as the destination for the export.
The ExportAsFixedFormat method of the Worksheets object is the key to this macro. This method allows you to export the active sheet to a specified format.
The ExportAsFixedFormat method accepts a variety of arguments. The code in this tutorial uses the following;
Type: This is to specify that the sheets are to be exported in PDF format.
Filename: The files are saved to the directory that was created using the MkDir function. They are saved under the worksheet names.
OpenAfterPublish: This is set to False so that the files are not opened when the macro is complete.
The code is wrapped in a For loop to export each sheet as a separate PDF. The loop is set to start from the first sheet and export all sheets to PDF.
The start and end point of the loop can be adjusted if there is a specific range of sheets that you wish to export.
More Useful Excel VBA Tutorials
Macro to export multiple sheets to a single PDF
Excel VBA Evaluate function
A Beginners guide to For Loops
5 ways to use the Immediate window
Hi Alan, When I try and run the code, I get a run-time error 75
Path/File access error?
Hi Chris,
This sounds like a problem with the line below.
FolderPath = “C:UsersComputergagaDesktopPDFs”
You will need to adjust the Folderpath to match your own system. For example Computergaga would be your username.
If you have already personalised this to your setup then the other possible reason is that you have run the macro more than once and the folder already exists.
Alan
I have an excel sheet that I need to produce the PDF file but I need it not to overwrite the one this code creates. Every time I hit the pdf button to create another one it replace the previous one. How would I get it to create a new one each time. I do have it saving with the work order number right now and that would be great if I can save the pdf with the work order number also. My form automatically generates a new number using the (+1) so it would go from 100 to 101 and so-on as I save the files. I would like to do that with pdf also. Here is the excel code used to create the excel sheets ——– Range(“K1”).Value = Range(“K1”).Value + 1
You should be able to concatenate the Range(“K1”).value onto the filename code in the ExportAsFixedFormat method. You are pretty much there. Just concatenate what you have on the end and this should be a new PDF each time.
Hi, how to make every time the pdf save,the old one would not be overwrite by the new one and the name will be in sequence?
You will need to find the old version of the PDF in the list. Then add 1 onto the old version number. If this is at the end the RIGHT function can locate it. Add 1 on top and concatenate them back together and save.
Hi there,
It was superb tutorial but I want to change it according to my needs and your help will be appreciated.
I want to collect some data from text box and combo box on vba form and then by pressing a button, i should be able to save selected data to a PDF file in xyz directory.
Many thanks
Regards,
Saif
Hi,
Thank you for your contributuion. It reallyhelped me in solving my needs.
But I have a question, the code which you provided to me I have to go in visual basic and rewrite it again and again whenever I want to create a new excel file.
How can I have the code present in the excel file whenever a new excel sheets is exported from a system?
Also, is there any other way to run and convert the files to PDF without going to visual basic?
Thanks,
Daljit Rahsi
If the macro is in the Personal Macro Workbook it will be available to any Excel file you need it to. As long as the macro is written generally and not referring to sheets or files by name it should work at any time.
No you need a macro unless there is some third party software that can also achieve it.
Hi, really appreciate your video tutorials on youtube, my vb skills are slim to none.
Can you give me an example, say i have 3 worksheets and i want to just export 1 sheet called “graphs”, so not all of them. Where do i put this in your code?
You would not need the loop through each sheet or the variable for the sheet. You could simply open it using worksheets(“graphs”).open
This would happen before the export. The rest of the code would be the same
Thanks alot for taking time out to reply, very helpful, much appreciated.
keep up the good work, great stuff! 🙂
Thanks Rossy
This works great for what I want but I wondered if I can add additional variables? I have designated the sheet range (30 to 67) but within that range I would also like it to only convert to pdf, sheets with the word PRINT in cell F3
This is such a time saver! Thanks again
Looping through sheets 30 to 67 can be done with a simply For Next loop like below.
For i = 30 to 67
sheets(i).activate
next
The PDF export code from the video would be put within an IF statement to make it conditional like below.
If Range(“F3”).value = “PRINT” then
code to export to pdf
End If
Worked perfectly! Thanks
Hi Alan,
I am getting a error ‘1004’? how to get rid of it.
Please advise.
Thanks,
Subhranil
Hi Subhranil,
You could use the F8 key to step through the code line by line. At the point that it fails should help you identify the error.
Check the code again with the video also. It is very hard to tell without seeing what you have.
Alan
Hi alan,
How would I be able to modify this to only select a specific named worksheet in this code but still batch exporting from a specific file folder? I am very new to VBA and dont know too much but your videos have helped Tremendously.
This blog post shows you how to loop through the files of a folder and open one.
http://www.computergaga.com/blog/loop-through-all-files-in-a-folder-using-vba/
This code can be adapted to then export a specific sheet in each file by using the PDF code.
Hi Alan,
How to convert to PDF first three sheets repeating and fourth with fourth name as file name, first three and fifth with fifth name as file name etc….
Yes you can set the loop as For 1 to 3 rather then For 1 to worksheet.count.
You will then need an exterior loop for your fourth, fifth and however many more sheets you have. Using sheet names as file names is shown in the code. its all about this exterior loop.
Hi Alan,
Thanks for the update, i don’t have any idea about the coding or macro, i tried the above code, but it getting one sheet one PDF. please help me with code. My work book has 8 sheets, Sheet1, Sheet2,Sheet3,Employee1,Employee2…..Employee5.
I need to genertae Employee1.pdf, with Sheet1,Sheet2,Sheet3 and Employee1, then another PDF for Employee2 with Sheet1,Sheet2,Sheet3 and Employee2. Please help me, now i am doing manually each sheet and save as PDF.
Hi Biji,
This should work. You will need to copy it into a module and teak the folder path to your liking.
Sub ExportAsPDF()
Dim FolderPath As String
Dim EmpSheets As Byte
FolderPath = “C:UsersTrainee1DesktopPDFs”
MkDir FolderPath
For EmpSheets = 4 To 8
Sheets(Array(1, 2, 3, EmpSheets)).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & “” & _
Worksheets(EmpSheets).Name, openafterpublish:=False, ignoreprintareas:=False
Next
MsgBox “All PDF’s have been successfully exported.”
End Sub
Hi Alan,
sorry for the delay, i was on vocation. I tried and it worked nice, thank you very much for you help. Best wishes…..
I am trying to make the sheets 1 to 4 and it’s not working..any suggestions?
Change the worksheets.count bit to 4. So it will read For I = 1 to 4.
Hello! Great tutorial 🙂
I have one question, maybe you can help me out with this.
I need to export only the same 4 sheets in order into the same PDF (from sh 4 to 8, from the same workbook).
I tried to use what you answered to the user “Biji” but still couldn’t do it.
If you have some spare time, I would appreciate your help.
THANKS!
Hi Guzman,
The video link below will show you how to export multiple sheets to a single PDF.
https://youtu.be/BlzZdWco3bs
Alan
Instant reply! Your web has been added to favorites!
Thanks 🙂
Suscribed to you YouTube Channel as well 🙂
PS. I didn’t get any email notification of your answer. Don’t know what happened, by chance I enter the site again.
Hi ! Maybe you can help me with this. I’m trying to: loop through a folder of excel files, and export as PDF, sheets 2 to 5 of each file, the name of the PDF is located in Cells(1,1) of Sheet 1.
Sub export_PDF_Test()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ruta = ThisWorkbook.Path & “\”
archivo = Dir(ruta & “*.xlsx”)
RutaCompleta = ruta & archivo
Do While archivo “”
Set l2 = Workbooks.Open(RutaCompleta)
l2.Sheets(Array(2, 3, 4, 5)).Select
*** code: to export as PDF (exportasfixedformat) *** ???
l2.Close True
archivo = Dir()
Loop
I have tried many things but always get an error.
Thanks for the help!
Hi Guzman,
You want to combine the code you have showing how to loop through files in a folder with the code you have from the blog post below.
This shows you how to export to PDF. You seem to have the array sorted and using the cells(1,1).value as the PDF name.
Alan
Hi Alan!!
I don’t know why I’m not receiving notifications of your reply.
Silly me !!!
I was coding in Thisworkbook module and was always exporting thisworkbook.activesheet
Now I’ve inserted a module and code the following and apparently is working.
***
Do While archivo “”
Set l2 = Workbooks.Open(RutaCompleta)
l2.Activate
l2.Sheets(Array(2, 3, 4, 5)).Select
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=ruta & archivo
l2.Close True
Debug.Print archivo & ” archivo”
archivo = Dir()
Loop
***
I still have to add to take the name from A1, but that’s easy 😉
THANKS !!!
Hi Alan, to make clearer my solution I posted here again (in the reply section looks so small).
To loop through a folder of excel files and export as PDF, sheets 2 to 5 of each file.
I`ve reached to the following:
***
Sub export_PDF()
Application.ScreenUpdating = True
Application.DisplayAlerts = True
ruta = ThisWorkbook.Path & “\”
archivo = Dir(ruta & “*.xlsx”)
RutaCompleta = ruta & archivo
Do While archivo “”
Set l2 = Workbooks.Open(RutaCompleta)
l2.Activate
l2.Sheets(Array(2, 3, 4, 5)).Select
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=ruta & archivo
l2.Close True
Debug.Print archivo & ” archivo”
archivo = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
***
Sorry that I didn’t declare the variables.
Probably this code should be adjusted so the sheets to export is not a fixed number, but a variable.
Yes, depending on their circumstance.
Thanks for sharing Guzman. Much appreciated by all I’m sure.
Hello, Thanks for this help but I’m facing Run time error 5 ” Invalid procedure call or argument.”
Please help me with this.
Thanks in advance !