Create a PDF file for each Excel Worksheet

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)

22 thoughts on “Create a PDF file for each Excel Worksheet

  1. Janine

    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!

    Reply
  2. 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. 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

    Reply
  3. Lythe

    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 🙂

    Reply
    1. Frank 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:

      < (less than)

      > (greater than)

      : (colon)

      ” (double quote)

      / (forward slash)

      \\ (backslash)

      | (vertical bar or pipe)

      ? (question mark)

      * (asterisk)

      Reply
  4. Lythe

    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!

    Reply
    1. Frank Post author

      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.

      Reply
  5. Webb

    CAN YOU MAKE EACH NAME OF A WORKSHEET DIFFERENT I NEED THE SAVE TO APPEAR AS “Sheet Name””Full Spreadsheet name”

    Reply
    1. Frank 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:

      • < (less than)
      • > (greater than)
      • : (colon)
      • ” (double quote)
      • / (forward slash)
      • \ (backslash)
      • | (vertical bar or pipe)
      • ? (question mark)
        • (asterisk)
      Reply
  6. Nik

    Thank you very much for this. I’ve been trying to find something to create PDF’s from every worksheet for a while now.

    Reply
  7. Muskhal

    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.

    Reply
  8. rge

    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.

    Reply
  9. 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:=xlTypePDF, _ Filename:=Fname, _ Quality:=xlQualityStandard, _ IncludeDocProperties:=True, _ IgnorePrintAreas:=False Next ws End Sub

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *