Hi

See this KB

Copying Worksheet Programmatically Causes Run-Time Error 1004
http://support.microsoft.com/default...84&Product=xlw


--
Regards Ron de Bruin
http://www.rondebruin.nl


"ah666" wrote in message ...
Hi, wonder if anyone can help me. I have a Macro that generates a series of
worksheets in a single workbook based upon a sheet in the workbook which
contains a series of numbers. Each sheet contains information relating to the
number found in the first worksheet via a series of look ups.

However the problem relates to how many workbooks I can insert, when I first
developed the macro it ran fine and generated around 750 sheets. Now however
it keep stopping at 186 sheets, and gives an error that it can't create
another sheet, the basis of the macro I am using is :

x = 6 'the numer of sheets to start inserting new ones after

For a = 1 To 1000
Sheets("fap template").Copy After:=Sheets(x)
sh = x + 1
Sheets(sh).Select

'rename sheet and then do series of look ups and populate
Next a