Creating a series of workbooks from single workbook

I am trying to work out how to create a series of workbooks from a single workbook - let me explain

I have a workbook that carries out a series of actions and then saves the workbook with a new name, leaving the "template" in it's original format.

What I want it to do is save book1 then create book2 .....bookx based on a list in the template.

Can anybody point me in the right direction?

Thanks

Originally Posted by AndrewCrisp

I am trying to work out how to create a series of workbooks from a single workbook - let me explain

I have a workbook that carries out a series of actions and then saves the workbook with a new name, leaving the "template" in it's original format.

What I want it to do is save book1 then create book2 .....bookx based on a list in the template.

Can anybody point me in the right direction?

Thanks

First create a variable for the file name. Then you need a loop which contains the instruction to save the file name. The loop counter should be used to work down the list of file names in the worksheet, and each time the current name in the list should be assigned to the file name variable. So suppose the file name variable is stFName, the save instruction within the loop is then:

Please Login or Register  to view this content.

HTH

Thanks for this - I understand.

My only query is that in Excel normally once a file is "saved as" the original file is closed, so if I then close the "saved as" file will the original file, and therefore macro, still be active?

Thanks

Originally Posted by AndrewCrisp

Thanks for this - I understand.

My only query is that in Excel normally once a file is "saved as" the original file is closed, so if I then close the "saved as" file will the original file, and therefore macro, still be active?

Thanks

Hi,

Just because a file is 'saved as' doesn't mean that the original is closed.

This depends to some extent on the precise process here. If you're copying data and pasting it into a 2nd, 3rd etc.. workbook, and then saving and closing these additional workbooks, then the original file is not affected, and is still open in memory and hence the macro are still active.

If however you're starting with a 'template' .xls workbook, modifying it and saving and closing the modified file with a different name, then of course once you close it the macro is not available. In which case I'd be inclined to do a double save. Save it with the new name, then save it immediately with the original name.

HTH

It seems to work now. Next task is to automate distribution of created sheets using Outlook!

Originally Posted by AndrewCrisp

It seems to work now. Next task is to automate distribution of created sheets using Outlook!

Indeed,

This has come up fairly recently, and I remember posting a suggested solution for this on the forum, and no doubt if you google you'll find other references. I've used this functionality on many occasions. One of the nuisance factors was that Microsoft, being paranoid about virus distribution, put restrictions on automating these sorts of things, and you find that a splash screen usually opens 5 seconds after the .Send code, but which needs the user to answer 'Yes'.

I found a utility on line called 'ClickYes' which you can run in memory and which gets round this aspect. You may find that you can also use the 'SendKeys' code to overcome this although I've never tested that myself.

Good luck.........


Rgds

5 Nov 09!! Your one but last answer i.e. the one about "Save as" is not my experience.
Tested this a.m. Open workbook name x; made a change; saved as y. The button on the taskbar for x disappeared. When I closed y, excel asked if I wanted to keep the change. Said yes. Taskbar button disappeared. Then I opened x the change was not there, as expected.
Please explain "Just because a file is 'saved as' doesn't mean that the original is closed.
". I want to Save as and keep original workbook open.
Many Thanks