Forum Discussion
Solved
Saving worksheet as pdf with worksheet name and date stamp
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
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 SubRemark: characters such as / and : are not allowed in file names, hence the use of the Format function.
5 Replies
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 SubRemark: characters such as / and : are not allowed in file names, hence the use of the Format function.
- 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
