Creatn multiple sheets in single workbook

[Guest]

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

 

[Ron de Bruin]

Hi

See this KB

Copying Worksheet Programmatically Causes Run-Time Error 1004
http://support.microsoft.com/default.aspx?scid=kb;en-us;210684&Product=xlw

 

[Guest]

Thanks I have now got a series of sheets inserts, however I cant find a away
using sheets.add of selecting the location in a workbook I want the sheet
added, in my original formula I copied the sheets after sheet(x) but cant see
how to add the sheet after sheet x

 

[Ron de Bruin]

Try this

x = 6 'the numer of sheets to start inserting new ones after
For a = 1 To 200
Sheets.Add Type:="C:\aaa.xlt", after:=Sheets(x)
x = x + 1
Next