How to Save Excel sheets as PDF with Current Date Using VBA?

by

If you often find yourself converting Excel reports to PDF format and manually typing in the current date to keep your file versions in order, you will find this trick pretty useful. We will use VBA (Visual Basic for Applications) to automate this task, saving you time and reducing the risk of errors.

#1: Prepare your Excel workbook

First off, locate the Excel Spreadsheet that you would like to export to PDF. Then make sure that your development tab is enabled, so that you can access the VBA editor.

#2: Add your VBA code

Next, we will add a new VBA module that will include our automation code:

  • From your Ribbon, hit the Developer button.
  • Then hit ALT + F11 to open the VBA Editor (or press the Visual Basic button)
  • Then under the VBAProject hit Insert and then choose Module.
  • This adds a VBA module, where you can write your code.
  • Paste the following code into your module:
Sub ExportPDFWithDate()
    Dim Sheet As Worksheet
    Dim FileName As String
   
    ' Replace "SheetName" with your actual sheet name 
    Set MySheet = ThisWorkbook.Sheets("SheetName")  

    ' Get today's date 
    myDate = Format(Now(), "yyyy-mm-dd")    

    ' Format the current date and create the file name
    FileName = ThisWorkbook.Path & "\" & MySheet.Name & "_" & myDate & ".pdf"
    
    ' Save the sheet as a PDF
    Sheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FileName
    
    ' File successfully exported
    MsgBox  FileName & " was saved."
End Sub

#3: Run your Macro and check your results

  • Next, Hit the Save button.
  • Then hit ALT + F8 and select the macro named ExportPDFWithDate
  • If you received the Success message, navigate to the folder that contains your spreadsheet to find your PDF file.
  • From here you can either open your file, or attach it to an email.

#4: Saving multiple Excel sheets to PDF

An additional use case is to save multiple Excel worksheets as pdf files.

Wrap the following code in a VBA subroutine:

counter = 1
    ' Get today's date 
    myDate = Format(Now(), "yyyy-mm-dd") 

    ' Loop through your file worksheets
    For Each ws In ThisWorkbook.Sheets
        ' Create the filename with the current date
        FileName = FolderPath & ws.Name & "_" & myDate & "_"&counter& ".pdf"
        counter = counter +1
    ' Save each sheet as a PDF with the filename
    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FileName