MrExcel Publishing
Your One Stop for Excel Tips & Solutions

save a sheet to a book using cell reference that is a date

Posted by Stacy on March 22, 2001 1:09 PM

I am saving a worksheet into a new workbook and combining 3 cells to make up the file name. Cells A1 and A2 work fine. cell A3 is a date so I have to convert it (i guess). It reads "Feb-01" for february 2001. Here is what I have so works with the exception of giving me a date of Dec-99 everytime. I need to get A3 worked in there somehow. Thanks in advance!!
Dim fdate As Date
fdate = ActiveSheet.Cells(1, 3)

ActiveWorkbook.saveas FileName:= _
& Sheets("Sheet1").Range("A1") _
& " " _
& Sheets("Sheet1").Range("A2") _
& " " _
& Format(fdate, "mmm-yy") _
& ".xls", _
FileFormat:= _
xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _

Posted by Barrie Davidson on March 22, 2001 2:01 PM

What is the active cell when you are declaring your variable (fdate)? If it is A1, then your variable is looking at C2. The syntax (1,3) is referring to the cell that is one row down and three columns to the right of the active cell.

Try changing your variable declaration to: fdate = ActiveSheet.Cells(0, 3)

Posted by Dave Hawley on March 22, 2001 10:25 PM

Hi Stacy

Don't declare you variable as a Date. Try this code below instead.

Sub TryThis()
Dim MyFileName As String
Dim FDate As String


FDate = Format(Range("A3"), "mmm-yy")
MyFileName = _
Range("A1") & " " & Range("A2") & " " & FDate & ".xls"

ActiveWorkbook.SaveAs FileName:= _

End Sub


OzGrid Business Applications