VBA insert template fails randomly

[shippey121]

Hi all,

i have a sheet where the users type in summary information, after all data is entered they run a code which creates several new sheets from a template where the user then enters more data,

the problem im having is that the program randomly crashes with error code 1004 on this line

[/code]Sheets.Add After:=Worksheets(shts), Type:="c:\check sheet template.xlt"

for example if the summary sheet contains 20 items the code runs fine and inserts 20 copies of the template, it seems to become a problem when trying to insert 100+ sheets.

i dont think its the pc as ive tried on several,

the loop that inserts the templates is

x = Range("I200").End(xlUp).Row - 3

For nwsheet = 1 To x
shts = Worksheets().Count - 1

Sheets.Add After:=Worksheets(shts), Type:="c:\check sheet template.xlt"
ActiveSheet.Name = "Sheet " & nwsheet

Next nwsheet

sorry for the poor layout, i forgot the code tags

 

[Andrew Poulsom]

That's interesting. According to Microsoft the method you are using shouldn't cause the error that can happen when copying worksheets in a loop:

Copying worksheet programmatically causes run-time error 1004 in Excel

It's recommended as a workaround, but it seems that the error persists.

 

[shippey121]

Hi andrew,

yes i ran into the problem you suggested before, originally the template was part of the workbook and the code made duplicates of it, this failed, the new code works and usually only fails when there are quite a few to be inserted, but, as mentioned not always.

for the record im using XL2003 but have tried the code on 2007 and 2010 with the same random crashes ??

is there another way i could achieve what im after

 

[Andrew Poulsom]

You could try saving, closing and reopening the workbook periodically as suggested in the RESOLUTION section of the link I posted.

 

[shippey121]

thanks Andrew, never noticed that line was a link, tried that and after a few adjustments to incorporate it into my code it seems to be working, it does slow the process down but at least it works

thanks

 

[shippey121]

just an update,

the resolution posted in andrews link which was to save, close and reopen the file worked but slowed the job down, this was due to the files being stored on a server and not locally so had to save across the network several time.

i came up with my own work around to the problem which not only works but is considerably faster too.

the template sheets i was inserting consisted of a single sheet, which the code would insert multiple times. i altered the template file to contain as many template sheets as i would need, i then altered my code to open this file and select as many sheets as i require and copy them in one transaction to the new book, the code runs faster now but also the file creation is quicker due to only one file being read.

thanks