This tutorial looks at how you can create a macro in Excel to export all sheets to PDF. Each sheet of the workbook will be saved into the same folder. The name of the worksheet will be used as the filename of the PDF.
By creating a macro this seemingly repetitive task can be performed at the click of a button.
Although all sheets are used in this example. The macro can be adapted to include all except the first sheet, or some other variation.
The code below can be copied into a VBA module and adapted to your needs. An explanation for the main parts of the code can be found below.
Sub ExportAsPDF() Dim FolderPath as string Dim I as Integer FolderPath = "C:\Users\Computergaga\Desktop\PDFs" MkDir FolderPath For i = 1 To Worksheets.Count Worksheets(i).ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & "\" & _ Worksheets(i).Name, openafterpublish:=False Next MsgBox "All PDF's have been successfully exported." End Sub
The MkDir function has been used to create a directory (folder) on the Desktop. This folder is then used as the destination for the export.
The ExportAsFixedFormat method of the Worksheets object is the key to this macro. This method allows you to export the active sheet to a specified format.
The ExportAsFixedFormat method accepts a variety of arguments. The code in this tutorial uses the following;
Type: This is to specify that the sheets are to be exported in PDF format.
Filename: The files are saved to the directory that was created using the MkDir function. They are saved under the worksheet names.
OpenAfterPublish: This is set to False so that the files are not opened when the macro is complete.
The code is wrapped in a For loop to export each sheet as a separate PDF. The loop is set to start from the first sheet and export all sheets to PDF.
The start and end point of the loop can be adjusted if there is a specific range of sheets that you wish to export.