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 ws.Select 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:
http://www.chicagocomputerclasses.com/excel-classes/
Submit your review | |
Great macro, for those getting the error for ws.select, check that no Tabs are hidden, once I unhid them the macro ran perfectly
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.
Hello,
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.
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.
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
Excellent. Thanks 🙂
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.!
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!
Thank you!
Worked like a charm . thank you so much.
