VBA Express : Excel - Save Filename as Cell Value with Current Date

Excel

Save Filename as Cell Value with Current Date

Ease of Use

Intermediate

Version tested with

2000/XP

Submitted by:

Zack Barresse

Description:

Saves the workbook with a filename consisting of a cell value and current date as a string. Does not overwrite the original file.

Discussion:

Maybe you'd like to change the filename to what you have in a cell value (A1 in our sample code here), topped off with the current date. Great for daily reports, saving for emails or passing workbook on for reviews. With this macro you can! Simply run the code (probably done best from a PERSONAL.XLS file as it's 'Global' in Excel), from the macros Run screen or Assign to a button, shortcut key or shape.

Code:

instructions for use

Option Explicit Sub SvMe() 'Saves filename as value of A1 plus the current date Dim newFile As String, fName As String ' Don't use "/" in date, invalid syntax fName = Range("A1").Value 'Change the date format to whatever you'd like, but make sure it's in quotes newFile = fName & " " & Format$(Date, "mm-dd-yyyy") ' Change directory to suit your PC, including USER NAME ChDir _ "C:\Documents and Settings\ USER NAME \Desktop" ActiveWorkbook.SaveAs Filename:=newFile End Sub

How to use:

Copy the above code.
Open any workbook.
Press Alt + F11 to open the Visual Basic Editor (VBE).
From the Menu, choose Insert-Module.
Paste the code into the right-hand code window.
Close the VBE, save the file if desired.

Test the code:

Tools-Macro-Macros, and double-click SvMe.

Sample File:

SvMe.zip 6.05KB

Approved by mdmackillop

This entry has been viewed 508 times.