Excel vba save as pdf

As a consequence of this, the filename is simply the one that is specified by the user of the application by using the Save As dialog displayed by the GetSaveAsFilename method. The last row of the If… Then statement and the sample macro in general signals the end of the If… Then statement. As a general rule, whenever you want to save all of the worksheets within an Excel workbook, you can simply use the Workbook.

ExportAsFixedFormat method. The explanations I provide throughout this Excel tutorial particularly the first section are generally applicable to the Workbook. However, when you use the Workbook. This may be what you want in certain situations. However, in other situations, you may want to save each worksheet within a workbook in a separate file. You can achieve this by working with the Worksheet. ExportAsFixedFormat method and loops. In the context of the ExportAsFixedFormat method and saving Excel files as PDF, the following 2 are the main things you must understand in connection to loops :.

When properly structured, a loop allows your VBA code to go through every single worksheet and saves it as a separate PDF file. Let's take a look at a macro that combines the ExportAsFixedFormat method with loops in order to save each worksheet as a separate PDF file:. The following screenshot shows the new items, which I explain further below. This statement declares a variable called iCounter. The selected data type is Integer which, as explained in this tutorial , can store integers between , and 32, The iCounter variable is used as a loop counter , a topic I explain further when covering the following rows.

This is the opening statement for the loop. In this particular example, I use a For… Next loop. This is, however, not the only type of loop you can use for these purposes.

The end value of the counter, which determines when the looping stops, is specified by the Sheets. Count property. The value returned by Worksheets. Count is the number of worksheets. As a consequence of the above, the macro loops a number of times equal to the number of worksheets in the active workbook. As I explain above, this is the opening statement of the With… End With block that adjusts the page setup.

All of the statements within a With… End With block rows 4 to 10 make reference to the object that appears in this statement Worksheets iCounter. The difference between both statements is, therefore, on the specific Worksheet object used within the syntax of the Worksheet.

The purpose of this block is to adjust the relevant properties of the PageSetup object for the relevant worksheet as determined by the loop. I explain, in detail, this statement and all the parameters of the ExportAsFixedFormat method used therein in a previous section of this tutorial. There are, however, 2 rows that differ from what appears in the previous examples:. In both cases, the difference is in the use of the Worksheets collection and an index Worksheets iCounter to refer to the relevant worksheet.

After reading this Excel tutorial, you're ready to start creating macros for purposes of saving Excel files as PDF. In addition to the above, you've seen 10 different examples of VBA code that allow you to save Excel files as PDF using several different settings and parameters. As with most topics related to Excel and VBA, I've not exhausted the topic despite writing this very comprehensive tutorial.

You can use the knowledge and ideas you've gained here in order to create different macros that achieve similar purposes. Here are some of my most popular Excel Training Resources:. Among others, I also : Provide a thorough introduction to how you can use Visual Basic for Applications for purposes of adjusting the page setup attributes.

Explain how you can save each worksheet from an Excel workbook in a separate PDF file. The following table of contents lists the different sections of this blog post. Table of Contents. Most Voted Newest Oldest. Inline Feedbacks. Load More Comments. Specifies whether there are different headers and footers for odd and even pages.

Returns a Graphic object representing the picture in the center section of the footer. Use the properties of CenterFooterPicture to set the attributes of the picture. Returns a Graphic object representing the picture in the center section of the header. Use the properties of CenterHeaderPicture to set the attributes of the picture. Returns a Graphic object representing the picture in the left section of the footer. Use the properties of LeftFooterPicture to set the attributes of the picture.

Returns a Graphic object representing the picture in the left section of the header. Use the properties of LeftHeaderPicture to set the attributes of the picture. Returns a Graphic object representing the picture in the right section of the footer. Use the properties of RightFooterPicture to set the attributes of the picture. Returns a Graphic object representing the picture in the right section of the header.

Use the properties of RightHeaderPicture to set the attributes of the picture. When you use it with an object qualifier, returns an Application object representing the creator of that object. Returns a bit integer indicating the application in which the relevant object was created. Returns or sets the text alignment for the even pages of an Excel workbook or a section.

Returns or sets the text alignment for the first page of an Excel workbook or a section. Returns or sets the number of pages tall to which an Excel worksheet is scaled to when printed. Thank you again! Make sure that the name generated is correct. When I tested the code with some dummy values in the cells from which te name is taken, it ran without errors. With ThisWorkbook. Hello again everyone, I need help again, Please, Please I have spent hours trying to find VBA to to the following: 1 Save the active sheet of my Excel Template file as a file with specific cell references in the name.

Select Range "B2". I will really appreciate some help Thank you very much Disri. This thread is locked. You can follow the question or vote as helpful, but you cannot reply to this thread. I have the same question 0. See screenshot:. Press the F5 key to run the code. Now the active worksheet is saved as PDF file.

And the PDF file is named with the worksheet name. If the active worksheet is blank, you will get a dialog box as below screenshot shown after clicking the OK button.

Now a new Outlook email is created and you can see the PDF file is listed as an attachment in the Attached filed. Save Workbook in the same location of the Macro this Workbook You can save the workbook in the same directory of the macro workbook using ThisWorkbook. Save the Workbook You can simply save the file without changing its file name or path name using Save method.

Example Files You can download the example file and explore it. Overwrite an Existing Workbook using VBA — Solution: You can avoid this by disabling the alerts temporarily and save the workbook with the same name by setting the Application. Overwrite an Existing Workbook using VBA — An Example The following example will show you, how to overwrite a file by disabling the application alerts. Easily save a worksheet or multiple worksheets as separate PDF files at once:.

Loading comment The comment will be refreshed after Hello, Is it possible to find the name for pdf from a cell? Cell H4 And in Cell H4 i want it to collect from three different cells. Is this possible? Hi Jason, Sorry can't help you with that yet. You need to manually delete it after emailing it. Then, if the file was created, the macro shows a confirmation message with the file path and name. The previous macro creates a default name with a time stamp, based on the active sheet name.

It prompts you to select a folder for the saved PDF file, and you can change the default name, if you prefer something different. In the macro below, the default name is based on the values in cells A1, A2 and A3 on the active sheet. The PDF file is automatically saved in the current folder -- you are not prompted to choose a folder, and cannot change the default name.

The PDF file is automatically saved in the current folder, with no prompts. However, if a file with that name already exists in the current folder , a message asks if you want to overwrite the file. Click Yes or No in the message box. In one of the Excel forums on the Microsoft website, someone posted the following solution to the problem.