Macro that will do a "File-Save As"

[TinaF]

This is probably really simple - I want to record (or get) a macro that
will do a "File-Save As-'whatever is in cell D3.xls'"

I've tried to record such a macro myself and thought it would be easy -
but when I try to run it, it re-enters whatever was in cell D3 during
the record process and attempts to save the file with that same name
again.

Below is what was recorded during my "Record Macro" steps. How do I
get it to copy WHATEVER is in R3C4 rather than defaulting to 60333,
which is what was there during the record?

Sub SaveQuote()
'
' SaveQuote Macro
' Macro recorded 12/12/2006 by Tina Ferraro
'
' Keyboard Shortcut: Ctrl+Shift+S
'
Application.Goto Reference:="R3C4"
ActiveCell.FormulaR1C1 = "60333"
ChDir "I:\Revised Estimating"
ActiveWorkbook.SaveAs Filename:="I:\Revised Estimating\60333.XLS",
_
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

 

[Dave Peterson]

with activeworkbook
.SaveAs Filename:="I:\Revised Estimating\" _
& .worksheets("sheet9999").range("d3").value & ".XLS", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
end with

Use the correct sheet name.

 

[Bob Phillips]

Sub SaveQuote()
'
' SaveQuote Macro
' Macro recorded 12/12/2006 by Tina Ferraro
'
' Keyboard Shortcut: Ctrl+Shift+S
'
ActiveWorkbook.SaveAs Filename:="I:\Revised Estimating\" & _
Range("D4").Value & ".XLS",_
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[TinaF]

Sub SaveQuote()
'
' SaveQuote Macro
' Macro recorded 12/12/2006 by Tina Ferraro
'
' Keyboard Shortcut: Ctrl+Shift+S
'
ActiveWorkbook.SaveAs Filename:="I:\Revised Estimating\" & _
Range("D4").Value & ".XLS",_
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

This macro below worked great for doing a file-save as "whatever is in
cell d3".xls. Thanks!

Follow on question, what if I wanted to save the file as
"whatever is in cell d3 (space) whatever is in cell d4".xls?

I have an estimate number in cell d3 and an estimate description in
cell d4. I want the file to be saved as, for example, 677888 Test
Transformer.xls.


With ActiveWorkbook
.SaveAs Filename:="I:\Utilities Estimating\" _
& .Worksheets("quote").Range("d3").Value & ".XLS", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End With
End Sub

 

[Dave Peterson]

With ActiveWorkbook
.SaveAs Filename:="I:\Utilities Estimating\" _
& .Worksheets("quote").Range("d3").Value _
& " " & .worksheets("Quote").range("d4").value & ".XLS", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End With
End Sub

 

[TinaF]

With ActiveWorkbook
.SaveAs Filename:="I:\Utilities Estimating\" _
& .Worksheets("quote").Range("d3").Value _
& " " & .worksheets("Quote").range("d4").value & ".XLS", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End With
End Sub

Thanks again! Works beautifully.
Tina