Save as cell + date macro

[Sanne]

Hi,

I have an excel template workbook that I’m re-using. I would like to create a macro that saves a workbook as a copy of the existing template workbook, creates a new folder for the workbook I want to save (if not already existing) then include another statement to clear the data cells on the template workbook so that I can reuse it without having to close and re-open it:

1.
Create a copy of the template workbook and Save as:
Col "J1" (customer) + col "J3" (product) + today’s date.

2.
Where to save it:
C:\Client\(and the customer sub folder)
If the customer sub folder does not exist, then create a new folder with the customer name as in "J1".

3.
When saved, close the newly saved workbook and wipe out the information from template workbook so that I can re-use it again without re-opening it.

Anyone that can help me w. this?
I have found different options on the internet but nothing that does all of the above I'm asking for. I'm a newbie when it comes to macros so don't have the skills to come up w. a solution on my own.

Thanks & Regards,
Susanne

[ravishankar]

Hi
try these codes for a start

Please Login or Register  to view this content.

ravi

[Sanne]

Hi Ravi,

The code doesn't work. Also, this code doesn't search for the client sub folder, nor creating a new sub folder if non existent.

Don't know if what I'm asking for is even possible?

Regards,
Susanne

[mdbct]

I would suggest putting your code in a separate hidden workbook and then use the macro from that book to save your file and then re-open the template for the next customer. The advantage is that you won't be saving the macro with each file, you'll only be saving the file.

Another option would be to use an actual template file (xlt extension) instead of the xls version of the file. The advantage being there would be no need to worry about clearing the fields once the Save As occurs since the template file will revert back to it;s original form.

I've attached an example of the hidden workbook. When it's opened, it will create a new menu item called ClientSaver which will launch this macro (it will also be hidden ):

Please Login or Register  to view this content.

Using the Workbooks.Add and specifying a workbook allows for the use of an XLS file as the template file.

[Sanne]

Hi,

Thanks for your help. Perfect.

Regards,
Sanne