Memory problem from macro creating and naming sheets from Array?

**acj06** · 10-26-2011, 06:26 PM

In the attached Workbook I have a macro that copies a master sheet and then renames it according to a range in the 'Ctrl' sheet. Unfortunately before all the sheets can be created the memory fills up and the macro breaks.

If someone could tell me how to clear the memory after each sheet has been created it would be much appreciated! Also if possible it would be great to add some lines that check if the number of names in the list is longer than the existing no. of sheets, if so continue, if not exit.

Any help much appreciated.

Best

Alex

**JBeaucaire** · 10-27-2011, 12:37 AM

What is the error message you get?
What line of code is highlighted when you DEBUG at the macro error?
Where is the sample workbook? (Zip it up if necessary, or trim it down.)

**acj06** · 10-27-2011, 05:05 AM

Sorry, the attachment has failed twice now...here is the code:

Please Login or Register  to view this content.

The error message is:
Run-Time error 1004

Then points to:

Please Login or Register  to view this content.

Thanks for the help

**JBeaucaire** · 10-27-2011, 01:22 PM

1) The uploaded wb has no sheet named Master and no sheet named FX. Both of those truths result in the error you've seen.

2) The uploaded wb has no reference data to create arrays from

3) The beginning of the macro does not include instructions to the array commands on which worksheet to create those arrays from

4) The uploaded wb does not include the macro you've referenced.

**acj06** · 10-27-2011, 02:23 PM

Sorry:
I had to strip out the private information i.e loosing the ranges etc, I don't know why the workbook doesn't contain the macro as it does for me. The sheet names was an error...

I have got the macro working by using a template instead of copying a sheet. Using the code below:

Please Login or Register  to view this content.

This is fine, but Ideally i'd like to keep it in the workbook as this is a model that will be used by people that a) Don't have any knowledge of VBA and won't know how to point to the Template location...

I've attached a workbook that I just tested and works, it won't let me attach the template so insert a excel template file with one sheet named 'Master'...You will need to point to it's location in the code in 'Location of template'
Sorry for failing to do this before.

I think I want to copy a sheet in the sheet and then clear the clipboard after every iteration, whats your thoughts on this?
Regards
Alex