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:=xlTypePDFFilename:=FnameQuality:=xlQualityStandardIncludeDocProperties:=TrueIgnorePrintAreas:=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: (greater than): (colon) ” (double quote) / (forward slash) (backslash) (vertical bar or pipe)? (question mark). (asterisk). ↓. Elizabeth Zanetti 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.
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. Visual Basic 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.
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:=xlTypePDFFilename:=FnameQuality:=xlQualityStandardIncludeDocProperties:=TrueIgnorePrintAreas:=False Next ws End Sub. ↓. Post author 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 Fname = 'Annex 1.1.' & ws.Index & 'result' To something like Fname = ActiveWorkbook.Name & '-' & ws.Name 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: (greater than): (colon) ” (double quote) / (forward slash) (backslash) (vertical bar or pipe)? (question mark). (asterisk). ↓. Post author 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 Fname = 'Annex 1.1.' & ws.Index & 'result' To Fname = ws.Name & '-' & ActiveWorkbook.Name 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:. (greater than).: (colon). ” (double quote).
/ (forward slash). (backslash). (vertical bar or pipe).? (question mark). (asterisk). ↓. Steve Dutton 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:=xlTypePDFFilename:=FnameQuality:=xlQualityStandardIncludeDocProperties:=TrueIgnorePrintAreas:=False Next ws End Sub.