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 the file: 0019 Save Excel as PDF with VBA.zip
Contents
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
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.
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.
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:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- 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.
- 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:
Thanks for the brilliant post. I found it to be very educative.
Great, I’m pleased it helped.
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!
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.
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.
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.
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
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!
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.
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.
Thanks, just what I was looking for. Cristal clear explanation
Thanks, I’m glad I could help 🙂
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.
To use VBA, you need to turn on the Developer Tab:
Thanks for the guide.
How do I get the same name on the pdf as the workbook ?
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:
Cannnot find ANYWHERE that can tell me how to SELECT SPECIFIC WORKSHEETS and create MULTIPLE PDFs. Can you?
Sure – I’ve added a section in the post above: Loop through selected sheets.
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.
Thanks for the suggestion. If I ever look into it, then I’ll certainly be sharing it here.
Thank you for the very useful post! Please keep going!
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?
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").ValueBrilliant; that was exactly the fix. I put the file path in another cell and used the concatenate you suggested.
THANK YOU SO MUCH!!
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
i am using win10 64 bit
but error on
Selection.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=saveLocation
on
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.
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.
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.
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.
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?
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.
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!
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?
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.
Information provided is very educative. Thank you. How can I save data from excel into individual fillable pdf files? Thanks.
I’m not sure that’s possible directly in Excel. I think you would need to use a specific PDF tool to achieve that.
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
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.
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!
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.
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!
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.
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
I think I figured it out. No need to respond.
Great news – Thanks Teri
Actually, I guess it didn’t work. Do you have any suggestions for my question from 11/23/2020?
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?
Hi Esther – just change the FileName parameter to something like this:
Sheets("Sheet1").Range("A1").ValueThe sheet name and cell refence should be changed for your scenario.
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
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.
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.
That’s great news. It’s always good to hear that my site is achieving what I want it to do 🙂
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!
To reference B1 on a worksheet is reasonably simple.
ActiveSheet.Range("B1").ValueTherefore the line of code would become:
FileName = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & ActiveSheet.Range("B1").ValueHowever, 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.
You’re a genius, and a very helpful genius too! Thank you so much for your guidance!
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!
You should be able to use an Application.InputBox.
Change this:
For this:
To prevent overwriting existing files you will need to check that the file does not already exist. Try this post:
https://exceloffthegrid.com/vba-code-to-copy-move-delete-and-manage-files/
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?
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.
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
Is there a way to make it print more than one sheet into a single pdf file?