Excell Macro Help Please

  • Thread starter Thread starter Bill Kirk
  • Start date Start date
B

Bill Kirk

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
 
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
 
This macro assumes that the date you want appended to the file nam
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 Shee
"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
 
Back
Top