Repeated worksheet copy error

[VeryBadApplicationMaker]

I have a macro which allows a user to paste a list of equipment ID names into a range. The macro will then make a copy of a template worksheet for each equipment ID and add it to the workbook. I find that when I run this for more than 20 or so equipment IDs the macro will fail without an error. Even when I stop the macro I cannot manually copy worksheets. It seems to be a memory issue. If I save and close the sheet and re-open I can copy and paste manually again.

The particular section of code where the macro hangs is the third line of the for loop below.

For Each c In Selection.Cells
tName = c.Value
    Sheets("Template").Select
    Sheets("Template").Copy Before:=Sheets("Definition")
    ActiveSheet.Name = tName
 
...more operations...
 
Next c

Does anyone have any suggestions as to how to prevent this happening or more details on what is going on?

Many thanks in advance.

 

[Andrew Poulsom]

Welcome to MrExcel.

Look here:

http://support.microsoft.com/kb/210684

 

[VeryBadApplicationMaker]

Thanks for the welcome and the assistance.

I don't know how many different searches I put into Google but I never managed to pull up that entry. Thanks very much!

 

[Andrew Poulsom]

I searched for 'error copy worksheet' and it was first in the list.