BeforeSave code that appends date - THANK YOU!!!!

A

awrex

For those that have helped me out on a number of questions I have a nice
working bit of code. Nothing fancy but something that I feel I will use again
and again in the future.

It will append a datestamp to the filename and rename a worksheet tab with
date - edit as needed.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

On Error GoTo ErrorHandler
Application.EnableEvents = False
Application.DisplayAlerts = False 'Turns off pop if file is already in
the directory
Cancel = True

MsgBox "Saved as <YOUR FILENAME>" & Format(Now(), "yyyymmdd") & vbCr &
vbCr & _
ActiveWorkbook.Path, vbOK ' MSG with filename and filepath

Sheet1.Name = "As of " & Format(Now(), "MM-DD-YYYY") 'Renames worksheet
with appended date


ActiveWorkbook.SaveAs _
ActiveWorkbook.Path & Application.PathSeparator & _
"<YOUR FILENAME>" & Format(Now(), "yyyymmdd") 'Saves file to
path that file was opened in.
ErrorHandler:
Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top