Save as file name

[highndry]

Hi,
I know this has been answered in different ways , but I still can't figure it out.

I want to save "Dashboard-Template" to "Dashboard-11-30-05" it is in the same directory. In cell "B3" of my Instruction sheet in my workbook the user can update the date, and I want the Macro to use that cell. I have recorded the following Macro but how do I refer th the cell "B3"?

Sub Save()
'
' Save Macro
' Macro recorded 11/30/2005 by CONMHI
'

'
ChDir "S:\Acctg\CGIT\Management Summaries\FY2006"
ActiveWorkbook.SaveAs filename:= _
"S:\Acctg\CGIT\Management Summaries\FY2006\DashBoard-Template.xls", FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
End Sub

"S:\Acctg\CGIT\Management Summaries\FY2006\DashBoard-Template.xls", This is where I want it to say Dashboard-B3

Thanks

 

[Von Pookie]

Try:

"S:\Acctg\CGIT\Management Summaries\FY2006\DashBoard-" & Range("B3").Value & ".xls"

 

[sykes]

What's the correct name of the sheet containing B3?

edit:

Sorry Kristy - didn't see you there!!

 

[highndry]

I get a runtime error 1004.

" Microsoft excel cannot access the file S:\acctg\cgit\management summaries\fy2006\Dashboard-11-30-05

The file path does not exist....

Hmmm I formatted the cell to custom mm-dd-yyyy and the date shows as 11-30-2005, however in the macro it still points to 11/30/2005

 

[highndry]

The correct name of the sheet is "Instruction". I would like to save the whole workbook not just the sheet.

Thanks

 

[Von Pookie]

Sykes was simply asking what sheet contains the value you want to use. With your current code, it will use the value of B3 from the whatever sheet is active at the time the code runs

As to your question: changing the format of the date in the cell won't help--you'll need to format it in the code.

In place of Range("B3").Value, use

Format(Sheets("Instruction").Range("B3").Value, "mm-dd-yy")