SOLVED

Saving worksheet as pdf with worksheet name and date stamp

Contributor

Hi All, can someone please assist with the following VBA code. I added the button to save the worksheet as pdf to a specific folder, I would like to add a date stamp to the filename when saving. Example: Employee name 202108.pdf

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\Users\Documents\Sample.pdf", _
OpenAfterPublish:=True
Application.ScreenUpdating = True
End Sub

5 Replies
best response confirmed by Janedb (Contributor)
Solution

@Janedb 

For example:

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:="C:\Users\Documents\Sample_" & Format(Now, "yyyymmdd_hhmmss") & ".pdf", _
        OpenAfterPublish:=True
    Application.ScreenUpdating = True
End Sub

Remark: characters such as / and : are not allowed in file names, hence the use of the Format function.

@hans works100% thank you again
Is there a way to save this worksheet using a specific cell in the worksheet, for example, D10 so that the file name includes that cell's value as part of the filename, it gives an "Application-defined or object-defined error
Filename:="C:\Users\Documents\P58\P58 Itemised Billing_" & Range("D10").Value & ".pdf", _
I found the problem... just needed to change the .Value to .Text

@Janedb 

Good to hear that you found the solution.