automatically produce save as file name by value in a cell

[dittotharappel]

I would like excel to automatically suggest the save as file name and location for my workbook when I click the save, save as or close options. The file name should be a combination of values in cells A1 and A2 of Sheet1. The location should be a folder named "Bill" in C:/My Documents. I think I can do it with a beforesave code but I am new to vba. Please help me.

[royUK]

Something like this, make sure that the path is correct

Please Login or Register  to view this content.

[dittotharappel]

Thanks, but that doesn't work. Neither the filename nor the save folder appear automatically. I am working with Office 2007. I want to save the file with extension .xls and not as xlsx. Is it a problem with the default file format. The default format is .xlsx.

[royUK]

What do you mean "appear automatically", do you want the saveas file dialog to appear?

[dittotharappel]

Exactly. And thanks for the reply. I would like if the save as dialog box appears with the filename and the folder C:/Bill. If the folder doesn't exist "create it now?" should appear.
Sometimes but not always error 1004 appears when I choose the save as option. When I debug the line
ThisWorkbook.SaveAs fPath & Application.PathSeparator & fName & ".xls"
gets highlighted. But I have the folder right there and it is not write protected.

[royUK]

Your initial question didn't say that at all. The code tha i gave you simply automates the save wihout showing the dialog.

[dittotharappel]

I am sorry for the lack of clarity.

[royUK]

This should do what you need

Please Login or Register  to view this content.

[dittotharappel]

It works the first time. But if the folder is already created it returns an error. This is what I did: I put the code in a new module and inserted the following code in This Workbook object:

Please Login or Register  to view this content.

[royUK]

Try this ameneded code

Please Login or Register  to view this content.

I've also changed the main macro

Please Login or Register  to view this content.

[dittotharappel]

That is excellent. Thanks a lot.

[dittotharappel]

On checking it once again there seems to be a minor problem. In the save as dialog box if i click cancel the macro doesn't work anymore. I have to close and reopen the file to make the macro available again.

[shg]

A little tweak:

Please Login or Register  to view this content.

[dittotharappel]

Now it tries to save in the "My Documents" folder but throws up an error message. But using the first piece of code

Please Login or Register  to view this content.

with the beforesave code I had been using, ie,

Please Login or Register  to view this content.

produces the desired result without any error. I am a newbie to vba and so I dont understand why. Perhaps you will be able to tell me why. If you can explain it will be enlightening to us all. Thanks a lot for your prolonged help, royUK and shg. Without you I would still be groping in the dark.