Excel VBA – Export Each Worksheet to a Separate PDF – Macro

If you need to Export Each sheet to an individual .pdf file this Macro will do it for you.

It will go through all the sheets in your Workbook and save each one to a separate PDF file using the worksheet name as file name.


Sub ExportToPDFs()
' PDF Export Macro
' Change C:\Exports\ to your folder path where you need the diles saved
' Save Each Worksheet to a separate PDF file.

Dim ws As Worksheet

For Each ws In Worksheets
nm = ws.Name

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\Exports\" & nm & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False

Next ws

End Sub

Posted by Excel Instructor:

Submit your review

Create your own review

Average rating:  
 16 reviews
 by Sumaiyah
Great Macro

Great macro, for those getting the error for ws.select, check that no Tabs are hidden, once I unhid them the macro ran perfectly

 by Stacy
A Real Timesaver

I have large workbooks with over 240 tabs. This macro will produce 140 pdsf and then I receive the 1004 error. I have to move the remaining tabs to another workbook, then add the macro. However the macro will create another 1004 error after you hit F5. It's at this point I must delete the line "ws.Select", then press F5 and it creates the remaining pdfs.

 by Mike Mendoza
A few questions


Great macro. Just a few questions.

Can you tell me how I could name each file as something other than the WS name? Maybe from a range in the WS?

Also, is there a way to make this macro drive many other workbooks? I would like to use this macro to call other workbooks and generate PDF's from them.

 by Irakli
Great Macro

How it could be modified to take names from the cells of particular sheet?

For ex: Sheet named PDF has cells A1, A2, A3 with the names that will be assigned to each exported PDF.

 by jennifer davis

I had to delete the ws.Select to get it to start publishing i got the same error as the person below, however it does not save i get another error saying the workbook is open

 by Zeshan

Excellent. Thanks 🙂

 by Ritesh Oza
Facing an issue on 6th worksheet

Hi wonderful VBcode!!

But I am getting an error of

Run-time error '1004'

Method 'Select of object'_Worksheet' failed.

First five sheets exported successfully. But at sixth sheet I am getting this error. Thanks.!

 by Carin

It took me a few tries on the file name, but finally got it working! One more question - how do you change the code to use a cell in the file name as the tab character length is not long enough for the names I need, thanks!

 by Dim

Thank you!


Worked like a charm . thank you so much.

Page 1 of 2: