Excel:needed, module that'll open the SaveAs dialogue w/suggested name 1

[coolcarls]

So I have it almost all working.
All I need now is either:
1. When the SaveAs dialogue box opens is have a suggested name in the "File Name" text box. Right now (as always) it uses the name of the workbook (book1 or whatever it maybe).

or

2. ActiveWorkbook.SaveCopyAs "C:\Projects\Lists"
This works, but, in my "Lists" file I have maybe 15 or 20 different sub folders. For example..."C:\Projects\Lists\SanFrancisco"
and C:\Projects\Lists\LosAngeles"
and C:\Projects\Lists\Chicago"

So, one of the cells in my sheet (sheet2) cell "c23" will have the text of one of the cities. Could I have this module ....."C:\Projects\Lists"
have something at the end that would include the value in cells c23 on the end to actually automatically save the file in the "C:\Projects\Lists\Chicago" directory if the value of sheet2.cells ("c23") was Chicago


Preferably, I would like method #2, but will be able to sleep tonight if either one works.

I wrote and re-wrote that about 1000 times, I still don't know if it makes any sense!

Help me if you can....

As usual, thanks a lot.
Carl

 

[dwlerwill]

For number 2 try

Dim city As String
city = Range("C23").Text
ActiveWorkbook.SaveCopyAs "C:\Projects\Lists\" & city & "\"

David Lerwill
"If at first you don't succeed go to the pub"

 

[coolcarls]

Thanks David. That's SO much prettier than the solution I came up with.