Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.
Email Address * First Name *
By entering your email address you agree to receive emails from Excel Off The Grid. We'll respect your privacy and you can unsubscribe at any time.

Save Excel as PDF with VBA

VBA Code Snippets

Since Excel 2010, it has been possible to save Excel files as PDF documents.  PDF was then and continues to be, a common file format for distributing documents.

The code examples below provide the VBA options to automate the creation of PDFs from Excel.  They can be used on their own or as part of larger processes.

Rather than going from Excel to PDF, you might want to go the other way; from PDF to Excel.  Check out this post for possible solutions for that: Get data from PDF into Excel.

Download the example file

I recommend you download the example file for this post.  Then you’ll be able to work along with examples and see the solution in action, plus the file will be useful for future reference.

Download Icon
Download the file: 0019 Save Excel as PDF with VBA.zip

Saving Excel workbooks, sheets, charts and ranges as PDF

This section contains the base code to save Excel as PDF from different objects (workbooks, worksheets, ranges and charts).  From a VBA perspective, it is the ExportAsFilxedFormat method combined with the Type property set to xlTypePDF that creates a PDF.

Save active sheet as PDF

The following code saves the selected sheets as a single PDF.

Sub SaveActiveSheetsAsPDF()

'Create and assign variables
Dim saveLocation As String
saveLocation = "C:\Users\marks\OneDrive\Documents\myPDFFile.pdf"

'Save Active Sheet(s) as PDF
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=saveLocation

End Sub

Save active workbook as PDF

To save all the sheets from a workbook use the following macro.

Sub SaveActiveWorkbookAsPDF()

'Create and assign variables
Dim saveLocation As String
saveLocation = "C:\Users\marks\OneDrive\Documents\myPDFFile.pdf"

'Save active workbook as PDF
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=saveLocation

End Sub

Save selection as PDF

Sometimes, we only want to save a small part of a worksheet to a PDF.  The following code saves only the selected cells.

Sub SaveSelectionAsPDF()

'Create and assign variables
Dim saveLocation As String
saveLocation = "C:\Users\marks\OneDrive\Documents\myPDFFile.pdf"

'Save selection as PDF
Selection.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=saveLocation

End Sub

Save a range as PDF

The macro below saves a specified range as a PDF.

Sub SaveRangeAsPDF()

'Create and assign variables
Dim saveLocation As String
Dim rng As Range

saveLocation = "C:\Users\marks\OneDrive\Documents\myPDFFile.pdf"
Set rng = Sheets("Sheet1").Range("A1:H20")

'Save a range as PDF
rng.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=saveLocation

End Sub

Save a chart as PDF

The VBA code below saves a specified chart as a PDF.

Sub SaveChartAsPDF()

'Create and assign variables
Dim saveLocation As String
Dim cht As Chart

saveLocation = "C:\Users\marks\OneDrive\Documents\myPDFFile.pdf"
Set cht = Sheets("Sheet3").ChartObjects("Chart 1").Chart

'Alternative using the active chart
'Set cht = ActiveChart

'Save a chart as PDF
cht.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=saveLocation

End Sub

Notes for saving PDF documents

While the Filename property is optional, it is important if we want to know where the file has been saved and what it’s called.

  • If the Filename property is not provided, the PDF saves in your default folder location using the Excel workbook’s name with the .pdf file extension.
  • Where a file name is provided, but not a file path, the document saves in your default folder location with the name provided.
  • When the .pdf file extension is not provided, the suffix is added automatically.
  • If a PDF already exists in the save location specified, it will be overwritten.  It may be necessary to include file handling procedures to prevent overwriting existing documents and handle errors.

Selecting specific worksheets before saving as PDF

If more than one worksheet is active, the PDF created will include all the active sheets.  The following code selects multiple worksheets from an array before saving the PDF.

Sub SelectSheetsAndSaveAsPDF()

'Create and assign variables
Dim saveLocation As String
Dim sheetArray As Variant

saveLocation = "C:\Users\marks\OneDrive\Documents\myPDFFile.pdf"
sheetArray = Array("Sheet1", "Sheet2")

'Select specific sheets from workbook, the save all as PDF
Sheets(sheetArray).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=saveLocation

End Sub

Looping and saving as separate PDFs

To save multiple PDFs quickly, we can use VBA to loop through sheets or charts and save each.

Loop through sheets

The following macro loops through each worksheet in the active workbook and saves each as its own PDF.

Sub LoopSheetsSaveAsPDF()

'Create variables
Dim ws As Worksheet

'Loop through all worksheets and save as individual PDF in same folder
'as the Excel file
For Each ws In ActiveWorkbook.Worksheets

    ws.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=ThisWorkbook.Path & "/" & ws.Name & ".pdf"

Next

End Sub

Loop through selected sheets

The following macro loops through the selected worksheets in the active workbook and saves each as its own PDF.

Sub LoopSelectedSheetsSaveAsPDF()

Dim ws As Worksheet
Dim sheetArray As Variant

'Capture the selected sheets
Set sheetArray = ActiveWindow.SelectedSheets

'Loop through each selected worksheet
For Each ws In sheetArray

    ws.Select

    ws.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=ThisWorkbook.Path & "/" & ws.Name & ".pdf"

Next ws

'Reselect the selected sheets
sheetArray.Select

End Sub

Loop through charts

The following code loops through each chart on the active sheet and saves each as a separate PDF.

Sub LoopChartsSaveAsPDF()

'Create and assign variables
Dim chtObj As ChartObject
Dim ws As Worksheet

Set ws = ActiveSheet

'Loop through all charts abd save as individual PDF in same folder
'as the Excel file
For Each chtObj In ws.ChartObjects

    chtObj.Chart.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=ThisWorkbook.Path & "/" & chtObj.Name & ".pdf"

Next chtObj

End Sub
100 Excel VBA Macros

Do you know the fastest way to learn foreign languages?  It is to read, write, speak, and think in that language as often as possible.  Apart from speaking, programming languages are no different.  The more you immerse yourself in that language, the faster you will pick it up.

100 Excel Macros Book

Therefore, what most people like you need is lots of examples that you can practice.  That is why the 100 Excel VBA Macros eBook exists.  It’s the book for all Excel users who want to learn how to read and write Excel macros, save time, and stand out from their peers.  The book contains:

  • 100 example codes to practice reading and writing macros that will embed the language into your thinking.
  • An introduction to macros in Excel to ensure you can implement the VBA code in the book even if you have no prior knowledge.
  • Consistent code layout between examples to enable you to understand the structure and easily customize the code to meet your needs.
  • Downloadable workbook containing all the source code, so the examples can be added to your project to give you the benefit of VBA straight away.

Other PDF export options

When using ExportAsFixedFormat there are other optional settings available:

'Open the document after it is saved - options are True / False
OpenAfterPublish:=False

'Include the Excel document properties into the PDF - options are True / False
IncludeDocProperties:=True

'Does the created PDF adhere to the Print Areas already set in the 
'worksheet - options are True / False
IgnorePrintAreas:=False

'Set the output quality of the created document - options are 
'xlQualityMinimum / xlQualityStandard
Quality:=xlQualityStandard

'The page to start printing from. If Excluded, will start from the first page
From:=1

'The page to print to. If Excluded, will go to the last page
To:=2

Example using all the options

The code below demonstrates how to use all the options within a single macro.  These options can be flexed to meet your requirements.

Sub SaveAsPDFOptions()

Dim saveLocation As String
saveLocation = "C:\Users\marks\Documents\myPDFFile.pdf"

'Example using all the options
ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=saveLocation, _
    OpenAfterPublish:=False, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    Quality:=xlQualityStandard, _
    From:=1, To:=2

End Sub

Other fixed formats available

The Type property can also create XPS documents when it is set to xlTypeXPS, rather than xlTypePDF.  XPS is Microsoft’s own fixed file format, which is similar to PDF, but based on the XML language.  It is rarely used in the real world, but is an option if required.

Conclusion

Learning how to save Excel as PDF is a good time investment.  Each of these code snippets on there own are not that impressive.  However, when combined with the automated e-mail processes and document control, they are a real time saver.



Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Email Address * First Name *

By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.


Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

62 thoughts on “Save Excel as PDF with VBA

      • MarioPas says:

        Okay Ladies and Gents.
        What version of Excel VBA are you using?
        For the life of me, I use the exact code snippets illustrated and nothing works.
        Only thing I get is Run- time error 5: Invalid procedure call or argument when executing something as simple as
        ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=NameOfFile

        When I change the code to reflect the MS suggested format to
        ActiveWorkbook.ExportAsFixedFormat(Type:=xlTypePDF,FileName:=FileName)

        All I get is a compile Error “expected a =” or a Syntax Error.

        What am I doing wrong? It shouldn’t be that difficult for heavens sake!

        • Excel Off The Grid says:

          I just re-tested this code, and it works. The issue is likely to be that the folder path contained within the FileName does not exist. Therefore Excel can’t save the file in that location.

          • MarioPas says:

            Just tested this simple code:

            Sub PrintSelectionToPDF()
            Dim invoiceRng As Range
            Dim strfile As String

            ‘Setting range to be printed

            Set invoiceRng = Range(“A1:L21”)

            ‘setting file name with a time stamp.

            strfile = “invoice” & “_” & Format(Now(), “yyyymmdd_hhmmss”) & “.pdf”

            ‘setting the fulli qualified name. The resultent pdf will be saved where the main file exists.

            strfile = “C:\TestFolder\” & strfile

            invoiceRng.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=strfile, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=True, _
            OpenAfterPublish:=True

            End Sub

            The end result was:
            Run-time error ‘5’:
            Invalid procedure call or argument

            strfile value was “C:\testFolder\Invoice_20210306_171147.pdf”
            The folder existed.

            Set InvoiceRng = Range(“A1:L21”) refers to a mock Invoice content.

            Stepping into the code, everything works until the invoiceRng.ExportAsFixedformat … statement

            I’m at a loss of words here!
            This code is too simple to fail in a run-time error.

            I’m starting to think that his must be a VBA version issue, where they changed the statement syntax or compile from Excel 2007 to current. Or something like that.

          • Excel Off The Grid says:

            I tested your code and it works perfectly fine on Excel 365.

            If you’re still using Excel 2007, that might be your problem. I believe there was an additional Office Service Pack to get the PDF functionality into Excel 2007. If you’re using an add-in to create PDFs, then you will need to enable it as an external reference in the Visual Basic Editor, and the document object model will be different.

            Support for Excel 2007 finished in October 2017, so you’ll need to dig around in some older blogs to try to find a solution.

          • Mariopas says:

            BTW

            The PDF plugin is also installed.

            The is version

            Add-in: PDF Suite 2014 Excel 2007 plugin
            Publisher: Interactive Brands Malta Limited
            Location: C:\Program files (x86)\PDF Suite 2014\addin\ExcelPlugin.dll

          • MarioPas says:

            Also, just executed the following code snippet from above example No. 1:
            Sub SaveActiveSheetsAsPDF()

            ‘Create and assign variables
            Dim saveLocation As String
            saveLocation = “C:\TestFolder\myPDFFile.pdf”

            ‘Save Active Sheet(s) as PDF
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=saveLocation

            End Sub

            C:\TestFolder” exists.

            Execution resulted in:

            Run-time error 5:
            Invalid procedure call or argument


            I’m starting to think that this inconsistency in execution of simple code, is intentional on the part of MS.

            They are pushing off all users of ‘Purchased’ software, by creating incompatibility between older versions of s/w and newer versions of OSs and vba compilers. Those on the ‘pay to play’ 365 version of anything have working compatibility, while those that ‘had’ working code now experience
            incompatibilities.

            All hell broke loose on my devel environment, after my latest W10 update.

            I can’t explain it any other way.

            Anyone have any other more consoling thoughts? They are welcomed!

          • Excel Off The Grid says:

            I don’t think this is a ploy by Microsoft to make previous code not work. Too many businesses are relying on VBA for MS to trash it. Microsoft still allows Excel 4 Macros to be used in Excel, which were superseded in 1995.

            But if you are using Excel 2007, you are using a version of Excel which I don’t think originally supported PDF export.

        • MariosPas says:

          I had all the Add-Ins related to PDF enabled.
          The code still did not work.
          As anticipated, I got fed up and got on the ‘Pay to Play’ treadmill.
          All latest versions were installed without fail.

          NOW EVERYTHING IS WORKING!

          I guess I’m not as trusting as most. Just had to accept the inevitable and go with the flow.

          When I spoke to the MS Support tech assisting with the installation of Office 365 and explained the situation, we tested the same code on both versions of Excel while tech had control of my computer. v2007 did not work, while v365, same code exactly, worked. The tech went on to explain that many changes were made to compilers between versions of OS and S/W, too many to track which change would not work on prior versions of platforms and some functionality would have been lost.

          Thank you for your assistance though. It’s great to see that there are others out there that can assist those in trouble. It is greatly appreciated.

          Cheers and stay healthy.

  1. Allan Andersen says:

    I Bought Office 365 Personal that I expected tha VBA still was in Excel, but I cannot find. If it not there(As far I can See) I must of course pay for 1 year and then STOP. It there another way to get VBA with Excel in a another product.
    I am very very disappointed, because it was the only reason I bought this product.

    • Excel Off The Grid says:

      To use VBA, you need to turn on the Developer Tab:

      • Right-click on a space in the ribbon -> Click Customize Ribbon
      • Ensure the Developer options is clicked
      • Click OK
    • Excel Off The Grid says:

      Hi Frede

      ActiveWorkbook.Name would give you the name of the workbook. But you would also get the “.xlsx” on the end. So something like this would give you a PDF file name:

      Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".")) & "pdf"
  2. C'Niche says:

    Cannnot find ANYWHERE that can tell me how to SELECT SPECIFIC WORKSHEETS and create MULTIPLE PDFs. Can you?

  3. Patridk Murphy says:

    Good beginner article.
    It would be great to see you take this further and actually manipulate the pdf after generating it. Things like adding a menu, or bookmarks, modifying the file security, adding attachments to the pdf file, etc. are big winners.

    In my own experience this can be done but it requires the use of the Microsoft_JScript library and PDF object.

    Let me know if you take this on.

  4. Matt says:

    Thanks this has helped me save a ton of time!
    I have one question:

    I want the file name of my sheet to be a cell value (the invoice number) which I can do with :

    s = Range(“G6”).Value


    ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, Filename:=s, …..etc….

    BUT…
    The pdf goes to the location where the spreadsheet is. I would like it to go to a different folder. Is this possible?

    • Excel Off The Grid says:

      Read the section in the article entitled “Notes for saving PDF documents”, this explains how to save in different locations.

      For your scenario:

      You either need to add the full file path into G6 our you could concatenate together such as (assuming the folder path is in G7):

      s = Range("G7").Value & Range("G6").Value
      • Matt says:

        Brilliant; that was exactly the fix. I put the file path in another cell and used the concatenate you suggested.

        THANK YOU SO MUCH!!

      • Linda Peppin says:

        Hi, great article.

        I’m trying to do something similar but I can’t figure out where the line
        s = Range(“G7”).Value & Range(“G6”).Value fits into the code and what, if anything, it replaces.

        Thanks

  5. Jitender Modgil says:

    i am using win10 64 bit
    but error on

    Selection.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=saveLocation

    on

    • Excel Off The Grid says:

      I don’t believe there is any reason why Windows 10 64 bit would behave like that. Is the saveLocation valid? That’s the most likely cause.

  6. Lewis says:

    Thanks for the article, I do have one question. When I convert the spreadsheet to a PDF some of the sheets are oversized and are stretched across multiple pages. Is there a way to adjust the zoom before converting? Thank you.

    • Excel Off The Grid says:

      I think it’s based upon how the print settings have been applied, adjust the settings in the Page Layout menu, Page Setup and Scale to Fit sections.

  7. Kiran dupargude says:

    Hello Sir,
    I found this article very usefull, it really helped me a lot.
    Peoples Like you are giving opportunity to beginners like us to learn more things & make us more productive.

  8. Ted Whittier says:

    Great article as always. I have a project with multiple named ranges that I want to export to PDF. they are on various sheets in the workbook. I’m able to export them to different PDF files but I’d like to put them all in the same file. Can it be done?

    • Excel Off The Grid says:

      Hi Ted – interesting question:

      It’s not something I’ve tried, but I’ve got two ideas which may work:

      Idea #1
      You could try this:
      – Create named ranges called “Print_Area” to be the same range as the named ranges.
      – Select the worksheets
      – Export the selected worksheets as PDF

      I’ve not tried it, but that should be possible.

      Idea #2
      Here is a harder option, but should also work.
      – Copy and past the named ranges to images all on the same worksheet.
      – Position the images so that they each exist on one worksheet with page breaks in the right places
      – Export the worksheet as a PDF.

      Let me know if you get any success with them.

  9. Happy User says:

    thank you for this help!
    I made an invoce wich complete automatically to a register then to a pdf, even if it is on onedrive (it upload it perfecly, then open online)
    PERFECT!

  10. Alexandra says:

    Hello. So, if i were to loop through all worksheets in a single workbook to save each worksheet as a separate PDF file, how would i make sure that each PDF file has a separate/ distinct name that I need? Is it possible?

    • Excel Off The Grid says:

      The SaveName property determines the name of the file. Just create a string which includes use the worksheet name or a counter to create a unique name.

  11. Jeannette says:

    Information provided is very educative. Thank you. How can I save data from excel into individual fillable pdf files? Thanks.

    • Excel Off The Grid says:

      I’m not sure that’s possible directly in Excel. I think you would need to use a specific PDF tool to achieve that.

  12. Rick says:

    Hi everyone,

    I am using the looping through sheets example to output several sheets as 1 PDF per sheet and love that I found this macro. However, I wanted to include a value from cell A11 in the filename of each sheet but found that only the value from the first sheet is being used. This means that the Macro outputs the several sheets, each overwriting the the last as the macro goes along. Any idea how to get it working using the value in A11 (A text string) for each consecutive sheet?

    Here is my code so far:

    Sub Export_PDF()

    ‘Create variables
    Dim ws As Worksheet

    ‘Loop through all worksheets and save as individual PDF in same folder
    ‘as the Excel file
    For Each ws In ActiveWorkbook.Worksheets

    ws.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=ThisWorkbook.Path & “\my path\” & Range(“A11”).Value & “.pdf”

    Next

    End Sub

    • Excel Off The Grid says:

      I’ve not had change to test this, but if you change this:

      ...& Range("A11").Value & ".pdf"

      To this:

      ...& ws.Range("A11").Value & ".pdf"

      it should work.

  13. Mark says:

    Looking to automate the process of filling customer info and printing contracts (~15,000). To do this I have set up the contract template in a worksheet and have lookups to fill in the customer info.

    Where I am stuck is saving the range (of the contract) as pdf and naming it the unique customer ID (to prevent overwriting).

    Is is possible to loop this process of filling the customer info, saving as pdf and printing while moving down a list of customer IDs?

    This is a loaded question, but your insights would be greatly appreciated!

    • Excel Off The Grid says:

      Yes this is possible

      – Loop through the cells containing the customer list. Within the loop include:
      – Calculate the worksheet so that it refreshes values in the contract template
      – Save the template as PDF using the customer name within the file name.

      This will be a great way to learn how to use VBA to automate your processes.

  14. Teri Vavricek says:

    Hello. This was very helpful, thank you! I wondered if you could help me with a problem I am having. I have a macro-enabled Excel template that multiple users will be accessing from a shared network drive. When they run the macro to save the Excel file as a PDF, I want it to save to their own desktop, so using a file path such as “C:\Users\TeriVavricek\Desktop\File.pdf” (which works if iIwant to save to MY desktop) in the file name will not work. Is there a way to code the save location to be that of the desktop of the user currently using the file? Thank you!

    • Excel Off The Grid says:

      The following should give you the file path to the Desktop folder

      Dim FileName As String
      FileName = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\File.pdf"

      You can then use the FileName variable within the macro.

      • Teri Vavricek says:

        Thank you for the quick response! I still seem to be a little stuck. Below is my original code. What it accomplished was exporting 3 sheets to PDF on my (TeriVavricek) desktop with the file name set as the value of cell N2 on the Auto UW Documentation sheet. I’m trying to figure out how to incorporate the code you provided into what I have here. I would appreciate any additional assistance you can provide. Thank you!

        Sub ExportAsPDFAUTO()

        Dim strFilename As String
        Dim rngRange As Range

        Set rngRange = Worksheets(“Auto UW Documentation”).Range(“n2”)

        strFilename = rngRange.Value & Format(Now(), “yyyymmdd”)

        Sheets(Array(“Account UW Documentation”, “Loss Analysis”, “Auto UW Documentation”)).Select
        Sheets(“Account UW Documentation”).Activate
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        “C:\Users\TeriVavricek\Desktop\” & strFilename & “.pdf” _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False

        End Sub

  15. Esther says:

    HellO!! great information here.

    However, would love to receive help on this:

    i wanna save the pdf as a certain name, referring to a cell in the worksheet that contains a formula (referencing another cell)

    How should i code this?

    • Excel Off The Grid says:

      Hi Esther – just change the FileName parameter to something like this:

      Sheets("Sheet1").Range("A1").Value

      The sheet name and cell refence should be changed for your scenario.

  16. Michel Lhost says:

    Hi,
    I’m trying tu use this formula but need to rename my pdf file with a cell value

    Sub SaveAsPDF()

    Dim saveLocation As String
    saveLocation = “C:\My Data\ myPDFFile.pdf”

    ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=saveLocation, _
    OpenAfterPublish:=False, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    Quality:=xlQualityStandard, _
    From:=1, To:=2

    End Sub

    When I add a cell value like this, it doesn’t work
    &.Range(“A1”).Value

    Can somebody help me pls ?

    Thx in advance and happy new year

    • Excel Off The Grid says:

      I’m guessing the syntax you’re using is incorrect. Try something like this.

      Filename:= Workbooks("myWorkbook").Sheets("mySheet").Range("A1").Value, _
      

      Change the object hierarchy to meet your specific needs.

  17. Megan says:

    I love you! I’ve got no macro or programming experience but was thrown into a project 17 days ago. Your page has taught me so much (with context so I actually understand HOW the codes work.) Thank you for hosting this page.

  18. Andy says:

    Dear Expert, I would like to request for your help please.
    I gathered the following codes from your guidance above:
    ==========================
    Dim FileName As String
    FileName = CreateObject(“WScript.Shell”).SpecialFolders(“Desktop”) & “\File.pdf”

    ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    FileName:=FileName, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=False, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
    ==========================

    The file successfully got generated but the filename is “File.pdf”.

    If I want the filename to be referring to cell “B1” on my worksheet, how should I amend the codes?

    I may sometimes rename the worksheet but the cell reference will always be “B1”.

    Thanks!

    • Excel Off The Grid says:

      To reference B1 on a worksheet is reasonably simple.

      ActiveSheet.Range("B1").Value
      

      Therefore the line of code would become:

      FileName = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & ActiveSheet.Range("B1").Value
      

      However, the challenge really comes if the worksheet name changes. The code above uses the ActiveSheet, which means the code only runs correctly if the worksheet containing the cell reference is always the active sheet when the code is executed.

      But if you want the code to run in the background on a sheet where it’s always the same sheet, but may have a different name, then using the sheet’s code name might work better. Check out this blog post:
      https://www.wiseowl.co.uk/blog/s112/microsoft-excel-vba-worksheet-sheet-name-codename.htm

      However, if the worksheet itself can change, then it may be better to use the Application.InputBox to get the cell reference at run-time. Check out this blog post:
      https://www.thespreadsheetguru.com/blog/vba-to-select-range-with-inputbox

      Hopefully, one of those will get you an answer suitable for your scenario.

  19. Daniel says:

    Thankyou so much for your code, super useful!

    One thing I was looking at adding into my code, was that the User could name the file when executing the macro. I currently have it saved to the desktop, but wanted them to be able to name it, or use consecutively without having to rename the original.

    This is your code I am using so far:

    Sub Save_Excel_As_PDF()

    Dim saveLocation As String

    MyPath = Environ(“USERPROFILE”) & “\Desktop\”

    saveLocation = MyPath & “New Equipment List.pdf”

    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
    FileName:=saveLocation

    End Sub

    Any help would be greatly appreciated!

  20. Rommy Setiadi says:

    Hi,

    Any mechanism, how to remove the “print to the pdf file” button after the sheet is printed? I’m just adding one button the name “print to the pdf file” after I click this button, the process will be executed, I get a pdf file including the “print to pdf file” button

    Any suggestion for this?

    • Excel Off The Grid says:

      Hi Rommy,

      Yes, that’s simple enough.

      Right-click the button. Press Ctrl + 1 to open the Format Shape settings. In the Size and Properties section, uncheck the option entitled “Print object”.

      Then it won’t appear when printing.

  21. Jeroen says:

    I have tried out the code and it works, except for the fact that my sheet is oriented landscape and the PDF file is Portrait every time. How can I solve this? Thanks

Leave a Reply

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