Sometimes it’s nececessary to generate a separate PDF file for each worksheet within an Excel workbook. This can be a big waste of time; especially if there are many sheets and/or you have to regenerate the PDFs every time the content changes!
If you are using Microsoft Office 2007 or later, the following VBA macro automates the process. The Fname variable can be used to customize the PDF file names that are generated.
Option Explicit
Sub createPDFfiles()
Dim ws As Worksheet
Dim Fname As String
For Each ws In ActiveWorkbook.Worksheets
On Error Resume Next 'Continue if an error occurs
' Name PDF files based on the worksheet Index (e.g Annex 1.1.1, Annex 1.1.2, etc.)
Fname = "Annex 1.1." & ws.Index & "_result"
' If you want to name the PDF files differently just change the Fname variable above to
' whatever you like. For example if you changed Fname to:
'
' Fname = “C:\myFolder\pdfs\” & ActiveWorkbook.Name & "-" & ws.Name
'
' The files would be stored in C:\myFolder\pdfs, and named using the
' spreadsheet file name and the worksheet name.
'
' WARNING: Using worksheet names may cause errors if the names contain characters that Windows
' does not accept in file names. See below for a list of characters that you need to avoid.
'
ws.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=Fname, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False
Next ws
End Sub
The following characters are not allowed in Windows file names, they will cause the macro to fail if you use them:
< (less than)
> (greater than)
: (colon)
” (double quote)
/ (forward slash)
\ (backslash)
| (vertical bar or pipe)
? (question mark)
* (asterisk)
Used this genius code (plus a dozen or so other useful pages) to beat my Excel invoicing into auto naming pdf exporting magic!
Mucho thanks.
Excellent….thank you!!!
Worked perfectly, using Excel 2007! Thanks!
Thank you so very much! this saved me a lot of time and effort.
I have used this to automate creating PDFs of a workbook with 50+ tabs. Worked great. However, I am wondering if there is a way to tell the macro which file directory/location to place the files? It seems to put the files into the same location as the first excel sheet I have open on my comptuer. Thanks!
Hi,
I am new to VBA. I am not sure which code to use for using a worksheet name. All the worksheets contain tech data and have different names. I put in the following from your code above and nothing happened. I would appreciate some help. Thanks Elizabeth
Option Explicit
Sub createPDFfiles() Dim ws As Worksheet Dim Fname As String For Each ws In ActiveWorkbook.Worksheets On Error Resume Next ‘Continue if an error results ws.ExportAsFixedFormat _ Type:=xlTypePDF, _ Filename:=Fname, _ Quality:=xlQualityStandard, _ IncludeDocProperties:=True, _ IgnorePrintAreas:=False Next ws End Sub
Thanks!! Great!
Hi,
It’s works really nice! There is one question I have. Is it possible to save the files as the name the worksheet has? Not the sheetnumber?
Hope to hear from you!
Nice job 🙂
Yes you can. All you have to do is set the Fname variable to include the spreadsheet file name.
In other words you would change the line
To something like
But please be aware that this will cause errors if the worksheet name contains characters that are not allowed in file names.
The following reserved characters are not allowed in Windows file names:
< (less than)
> (greater than)
: (colon)
” (double quote)
/ (forward slash)
\\ (backslash)
| (vertical bar or pipe)
? (question mark)
* (asterisk)
Another question; is there a way to change the location of the file. Since I’m saving about 100 sheets my desktop gets really full!
Thanks in advance!
This one I solved already 🙂 The other question still stands!
Thanks!
If you want to send the output files to a specific location, you can add an absolute path to the beginning of the Fname variable.
To do that change the following line
Fname = “Annex 1.1.” & ws.Index & “_result”
to
Fname = “c:\myFolder\result” & ws.Index & “_result”
Of course “c:\myFolder\result” is just an example, choose whatever folder and file name you like.
CAN YOU MAKE EACH NAME OF A WORKSHEET DIFFERENT I NEED THE SAVE TO APPEAR AS “Sheet Name””Full Spreadsheet name”
Yes you can. All you have to do is set the Fname variable to contain the sheet name and the spreadsheet file name.
In other words you would change the line
To
But please be aware that this will cause errors if the worksheet name contains characters that are not allowed in file names.
The following reserved characters are not allowed in Windows file names:
for each sheet
Thank you very much for this. I’ve been trying to find something to create PDF’s from every worksheet for a while now.
Thank You!
Hi, I am new to VB, it would be really great if someone could give me step by step guide..sorry to be a pain. I had tried to copy paste the code published, however it does not do anything.
Your help is very much appreciated.
Hi,
There is a good tutorial for running VB in Excel at http://www.contextures.com/xlvba01.html.
You’ll only need to the steps given in “Copy Excel VBA Code to a Regular Module”.
Hope this helps, Frank
Hi I don’t usually do this but your code helped me heaps. Thanks!
Many many many thanks! Works like a charm. Required a bit of tuning your own situation, but it’ll save so much time. I’ve bookmarked this site now and hope to find more useful tools. Tnx.
I’m using Excel on a Mac. I altered your code on the Fname line, but I’m getting run-time errors. Can you please help me sort out this code?
Also, I only need to generate PDF’s on about 30 worksheets out of 70 . . . do I first select the worksheets I want this to run on and then run the macro?
Here’s what I used:
Option Explicit Sub createPDFfiles() Dim ws As Worksheet Dim Fname As String For Each ws In ActiveWorkbook.Worksheets On Error Resume Next ‘Continue if an error occurs Fname = “/Users/sdutton/Desktop/ExcelPDFReports/” & ws.Name & “.pdf” ws.ExportAsFixedFormat _ Type:=xlTypePDF, _ Filename:=Fname, _ Quality:=xlQualityStandard, _ IncludeDocProperties:=True, _ IgnorePrintAreas:=False Next ws End Sub