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 far.....it 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)

Sheets("Sheet1").Select
Sheets("Sheet1").Copy
ActiveWorkbook.saveas FileName:= _
"C:\WINDOWS\DESKTOP\" _
& Sheets("Sheet1").Range("A1") _
& " " _
& Sheets("Sheet1").Range("A2") _
& " " _
& Format(fdate, "mmm-yy") _
& ".xls", _
FileFormat:= _
xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=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

Sheets("Sheet1").Copy

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

ActiveWorkbook.SaveAs FileName:= _
"C:\WINDOWS\DESKTOP\" & MyFileName

End Sub

Dave

OzGrid Business Applications