Need VBA Help...adding and deleting 40 sheets

[gritter]

I'm stuck. I am hoping someone can point me in the right to resolve this.

I am working on a project using Excel 2003. I am programmatically
adding about 40 worksheets to the workbook, loading them with data from
text files. I am using a hidden sheet as a template for each of the new
sheets and another sheets as the initial import location for the data
from the text file. I then programmatically copy & paste the data from
the temp sheet to the appropriate location in the newly create sheet. I
loop thru this process until I complete all of the required 40 sheets.

Everything works fine the first time I run the routine to create the sheets.

I then run a routine that deletes all 40 sheets. I do this by passing
an array of sheet names to the worksheet object - delete method. This
also works fine the 1st and sometimes 2nd time I use it.

The error happens when I run the routine to add and then delete the
sheets multiple time...something that the eventually user may need to do.

The error I get if it fails while creating the sheets is:

Run-time '1004':
Copy method of Worksheet class failed

The error occurs at the code that actually creates a new copy of the
template sheet

Sheets("template").Copy Before:=Sheets(7)

Usually it throws this after creating 60% of the sheets . I suspected
that I was not releasing an object while looping. But I've poured over
my code and I don't see it. Also, it runs fine the first time thru.
Wouldn't all of the objects be release after the VBA execution thread
terminates?

The only way I can clear this is to shutdown Excel and restart it.

I also inserted code to debug.print the memory usage. I suspected that
something wasn't being released and eating up all of my memory, but the
memory looked ok.

The entire routine follows... I appreciate any help.

--------------------------------------------------------------------------

Sub LoadPlateData()
Dim LLN As Integer 'last line used
Dim intRow As Integer 'current row
Dim strRange As String
Dim path As String
Dim prompt As String


mode = ALL
prompt = ""
path = GetPath(prompt, mode)

Application.ScreenUpdating = False

Set shtRefer = Sheets("Refer")
Set shtTmp = Sheets("tmp")
shtTmp.Visible = True

'find last used row in hidden reference sheet
LLN = Application.CountA(shtRefer.Range("A:A"))

'turn off automatic recalculation while template is being duplicated
Application.Calculation = xlCalculationManual

Application.CutCopyMode = xlCopy

'step thru list of data sheet backwards. Backwards because each
sheet is insert in
' front of previously created sheets. Finished group of data
sheets will be in
' same order as listed in reference sheet. To change order,
rearrange list on refer sheet

For intRow = LLN To 2 Step -1

'Create new data sheet from template

DoEvents
Sheets("template").Copy Before:=Sheets(7)
DoEvents

Sheets("template (2)").Visible = True
Sheets("template (2)").Select
ActiveSheet.Name = shtRefer.Cells(intRow, 1).Text


'import plate data from data files
DoEvents
Call ImportData(shtRefer.Cells(intRow, 4).Text,
shtRefer.Cells(intRow, 1).Text, Start520Data, Start730Data, path)
DoEvents
'Debug.Print "Loop " & intRow & " Mem: " & GetMemoryLoad() & "%"

Next intRow

shtTmp.Visible = False
Application.Calculation = xlCalculationAutomatic
Application.Calculate
DoEvents
Application.ScreenUpdating = True

'Debug.Print "end: " & Time()

'dereference obj variables
Set shtRefer = Nothing
Set shtTmp = Nothing


End Sub

 

[Gary''s Student]

The internal sheet counter may be getting screwed up. This error message
appears if you try to make too many copies of a sheet:

Sub dural()
For i = 1 To 9999
Sheets("template").Copy Before:=Sheets(7)
Next
End Sub