Change file name when saved to C drive

[Michael Lanier]

I want to change the name of my file "Estimation File" to "Master
File" when I save it to (or if it's already in) my C drive. Sheet1!A1
has a formula that returns either 0 or 1. Following are the
conditions:

If Sheet1!A1 = 0 and file is in C drive, then no change to file name
If Sheet1!A1 = 1 and file is in C drive, then change file name to
"Master File"
Chane occurs only when the file is saved in/to C drive

Thanks for any solution you might have.

Michael

 

[Don Guillett Excel MVP]

I want to change the name of my file "Estimation File" to "Master
File" when I save it to (or if it's already in) my C drive.  Sheet1!A1
has a formula that returns either 0 or 1.  Following are the
conditions:

If Sheet1!A1 = 0 and file is in C drive, then no change to file name
If Sheet1!A1 = 1 and file is in C drive, then change file name to
"Master File"
Chane occurs only when the file is saved in/to C drive

Thanks for any solution you might have.

Michael

Try this. I left in the "ask if overwrite" on purpose

Sub savefile()
If Sheets("Sheet1").Range("a1") = 1 Then
fn = "Master File.xls"
Else
fn = ThisWorkbook.Name
End If

ActiveWorkbook.SaveAs Filename:=fn, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

 

[Michael Lanier]

Don,

Thanks for your help. I modified your suggestion as follows:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If Sheets("Sheet1").Range("a1") = 1 Then
fn = "Master File.xls"
Else
fn = ThisWorkbook.Name
End If
ActiveWorkbook.SaveAs Filename:=fn, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

I placed it in ThisWorkbook. It changes the file name as expected,
but then I get a popup that states "Excel has stopped working . . ."
I'm still a novice so the problem is probably obvious to you. Also,
I'm using 2003 if that makes a difference but I also run it as a
compatible in my 2007 on occasion. Thanks in advance if you have any
further suggestions.

Michael