Excell Macro Help Please
 

Could anyone show me how to create a macro to save a workbook with file name
plus a date taken from a cell in a worksheet. That is "filename"+"date"
(where "filename" is the name of the workbook and "date" has been manually
input to a cell in one of the worksheets).

thanks

Excell Macro Help Please
 

Let's say that A1 thru A3 contain:
C:\sample
1_25_2006
..xls
and in A4 we put the formula
=A1 & A2 & A3
in A4 then we will see:
C:\sample1_25_2006.xls


in this simple example a2 will be updated with new dates

Then enter and run this tiny macro:


Sub Macro1()
Dim s As String
s = Cells(4, 1)
ActiveWorkbook.SaveAs Filename:= _
s, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
End Sub

--
Gary's Student


"Bill Kirk" wrote:

Could anyone show me how to create a macro to save a workbook with file name
plus a date taken from a cell in a worksheet. That is "filename"+"date"
(where "filename" is the name of the workbook and "date" has been manually
input to a cell in one of the worksheets).

thanks

Excell Macro Help Please
 

This macro assumes that the date you want appended to the file name
exists in a named range (DateRange) on Sheet1.

If the original filename was -Book1.xls -and the DateRange had 1/25/06,
the resulting filename would be -Book1_01-25-2006.xls-


Sub AppendDateToFilename()

Dim strDate As String
Dim strFullName As String

'Saves the file in the same directory with the text (date) from Sheet
"Sheet1" Rangename "DateRange" appended to the original filename
strDate = Format(Worksheets("Sheet1").Range("DateRange"),
"mm-dd-yyyy")
strFullName = Left(ActiveWorkbook.FullName,
Len(ActiveWorkbook.FullName) - 4) & "_" & strDate & ".xls"
ActiveWorkbook.SaveAs Filename:=strFullName _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub


Hope this is what you needed!


--
taylorm
------------------------------------------------------------------------
taylorm's Profile: http://www.excelforum.com/member.php...o&userid=28892
View this thread: http://www.excelforum.com/showthread...hreadid=505054