SOLVED

Saving worksheet as pdf with worksheet name and date stamp

%3CLINGO-SUB%20id%3D%22lingo-sub-2705941%22%20slang%3D%22en-US%22%3ESaving%20worksheet%20as%20pdf%20with%20worksheet%20name%20and%20date%20stamp%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2705941%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%20can%20someone%20please%20assist%20with%20the%20following%20VBA%20code.%20I%20added%20the%20button%20to%20save%20the%20worksheet%20as%20pdf%20to%20a%20specific%20folder%2C%20I%20would%20like%20to%20add%20a%20date%20stamp%20to%20the%20filename%20when%20saving.%20Example%3A%20Employee%20name%20202108.pdf%3C%2FP%3E%3CP%3EPrivate%20Sub%20CommandButton1_Click()%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20False%3CBR%20%2F%3EActiveSheet.ExportAsFixedFormat%20Type%3A%3DxlTypePDF%2C%20_%3CBR%20%2F%3EFilename%3A%3D%22C%3A%5CUsers%5CDocuments%5CSample.pdf%22%2C%20_%3CBR%20%2F%3EOpenAfterPublish%3A%3DTrue%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20True%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2705941%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2706131%22%20slang%3D%22en-US%22%3ERe%3A%20Saving%20worksheet%20as%20pdf%20with%20worksheet%20name%20and%20date%20stamp%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2706131%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F509992%22%20target%3D%22_blank%22%3E%40Janedb%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20example%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3EPrivate%20Sub%20CommandButton1_Click()%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20ActiveSheet.ExportAsFixedFormat%20Type%3A%3DxlTypePDF%2C%20_%0A%20%20%20%20%20%20%20%20Filename%3A%3D%22C%3A%5CUsers%5CDocuments%5CSample_%22%20%26amp%3B%20Format(Now%2C%20%22yyyymmdd_hhmmss%22)%20%26amp%3B%20%22.pdf%22%2C%20_%0A%20%20%20%20%20%20%20%20OpenAfterPublish%3A%3DTrue%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3ERemark%3A%20characters%20such%20as%20%2F%20and%20%3A%20are%20not%20allowed%20in%20file%20names%2C%20hence%20the%20use%20of%20the%20Format%20function.%3C%2FP%3E%3C%2FLINGO-BODY%3E
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

2 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