Macro to save file as different filename

[Guest]

Hello,

I have created a macro that is populating data from another sheet. Once the
data has been populated I created a macro that will paste all to values.
Now my problem, Below is where I am having a problem. Each time I run the
macro I want to save the file as a different filename. Is there a way to
have the macro popup and ask what filename I want to give it. I have to run
this macro on about 200 files. Any help will be greatly appreciated.

ChDir "C:\2007 Client data"
ActiveWorkbook.SaveAs Filename:= _
"C:\2007 Client data\2007 ?????? client data .xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
End Sub

 

[Ron de Bruin]

Hi Mac

have the macro popup and ask what filename I want to give it

Is there no way to get this information automatic (maybe a cell value or ?)

 

[Guest]

I am not sure how to do this. Could you give me an example. Again thank you.

 

[Ron de Bruin]

If you can give a me a example how you want to name the workbooks then
I can make a macro that loop through all workbooks for you and do your stuff and save
with a different name

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

I am not sure how to do this. Could you give me an example. Again thank you.

 

[Guest]

hi
you could replace your existing macro line with this...
Application.Dialogs(xlDialogSaveAs).Show
this would cause the save as dialog to pop up where you could give it a new
name.

Regards
FSt1

I am not sure how to do this. Could you give me an example. Again thank you.

 

[Guest]

Hi,
I know excel, but I am new to macros. I used the record macro to do the
macro. Could you tell me which line I shoud change to get it to work? I
feel like a real dummy. Is there a book out that would help me understand
macros better.

 

[Guest]

hi
change..
ActiveWorkbook.SaveAs Filename:= _
"C:\2007 Client data\2007 ?????? client data .xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False _
, CreateBackup:=False
to
Application.Dialogs(xlDialogSaveAs).Show

regards
FSt1