SaveAs Macro - Build filename from cell contents HOW?

[Guest]

I would really appreciate anyone who could show me macro code to accomplish
this.
I need to save workbooks with a filename generated from the values in
multiple cells. I need to fully automate this process up to the point where i
can browse to a save location, then hit save. The criteria is as follows..

- MUST specify (2) cells to gather data from to join to make 1 filename ie.
A1,A2 (saveas filename should look like "data1 - data2.xls" )

- MUST also specify worksheet in the workbook to grab the data from ie.
'SHEET2'

- MUST pop-up the 'browse' dialog, so i can select save location.

TIA!

 

[Guest]

Dim res as Variant, sName as String
with Worksheets("Sheet2")
sName = .Range("A1").Text & " - " & .Range("A2").Text
End With
res = Application.GetSaveAsfilename( InitialFilename:=sName & ".xls")

If instr(1,res,sname,vbTextcompare) = 0 then
msgbox "Please don't change the file name - action is halted"
exit sub
end if
if res = False then exit sub
Activeworkbook.SaveAs res

 

[Guest]

Tom, thanks very much for your solution!
I have modified the code to REMOVE the .XLS portion from the "Suggested
Filename", but the "save as type" defaults to "all files (*.*)"
So if i occasionaly choose to change the filname, it saves it with NO
extension.
I need it to default to (*.XLS)
(I also removed the "please dont change the filemane" option as i want to be
able to modify the filename from time to time)

Thanks again

 

[Guest]

Also, is there any way to have the BROWSE dialog start up to a specific path?
ie
UNC pathname or a different drive & path?

 

[Guest]

Look at help on GetSaveAsFilename for the filefilter argument to control the
saveas type.

modify
Activeworkbook.SaveAs res

to
Activeworkbook.SaveAs res, xlWorkbookNormal