Error 1004 after copying 84th worksheet to a new workbook...
 

Hi,
I've got a workbook which has a template sheet (shtCopy) and macros in it.
The macro scans data from many files and creates a 1-pager summary on each
file using the template (shtCopy) .

It puts all the 1-pagers into a single new workbook (wbFinal)

Everything seems to work fine until it gets to file/sheet #84.

Then the macro crashes at the following point giving error 1004.
shtCopy.Copy after:=wbFinal.Sheets(1)

Excel says the number of worksheets and named ranges are only limited by
memory of which I have plenty. The filesize up to this point is only about
3MB so it's really not that big.

Any ideas why I could be getting this problem?
I routinely deal with excel files well over 30 Meg so this is very odd.


Thanks for any idea,
MikeZz

Here's the routine that actually causes the error:

Private Sub Create_Contract_Summary(f)
'shtCopy
Dim DefSheets

If workbookCreated = False And CreateNewWB = True Then
workbookCreated = True
Workbooks.Add
DefSheets = ActiveWorkbook.Sheets.Count
Set wbFinal = ActiveWorkbook

Application.DisplayAlerts = False
Do While ActiveWorkbook.Sheets.Count 1
ActiveSheet.Delete
Loop
Application.DisplayAlerts = True
Set shtSummary = ActiveSheet
shtSummary.Name = "Summary"
ElseIf f = 1 And CreateNewWB = False Then
Set wbFinal = ThisWorkbook
End If

shtCopy.Copy after:=wbFinal.Sheets(1)
Set shtPaste = ActiveSheet

End Sub

Error 1004 after copying 84th worksheet to a new workbook...
 

It's not really clear what all your variables mean in the code you posted.
Is there any additional code that gives the result of those variables? I
figured it would be a memory issue, because you say it runs smoothly for all
other worksheets added.


--
Cheers,
Ryan


"MikeZz" wrote:

Hi,
I've got a workbook which has a template sheet (shtCopy) and macros in it.
The macro scans data from many files and creates a 1-pager summary on each
file using the template (shtCopy) .

It puts all the 1-pagers into a single new workbook (wbFinal)

Everything seems to work fine until it gets to file/sheet #84.

Then the macro crashes at the following point giving error 1004.
shtCopy.Copy after:=wbFinal.Sheets(1)

Excel says the number of worksheets and named ranges are only limited by
memory of which I have plenty. The filesize up to this point is only about
3MB so it's really not that big.

Any ideas why I could be getting this problem?
I routinely deal with excel files well over 30 Meg so this is very odd.


Thanks for any idea,
MikeZz

Here's the routine that actually causes the error:

Private Sub Create_Contract_Summary(f)
'shtCopy
Dim DefSheets

If workbookCreated = False And CreateNewWB = True Then
workbookCreated = True
Workbooks.Add
DefSheets = ActiveWorkbook.Sheets.Count
Set wbFinal = ActiveWorkbook

Application.DisplayAlerts = False
Do While ActiveWorkbook.Sheets.Count 1
ActiveSheet.Delete
Loop
Application.DisplayAlerts = True
Set shtSummary = ActiveSheet
shtSummary.Name = "Summary"
ElseIf f = 1 And CreateNewWB = False Then
Set wbFinal = ThisWorkbook
End If

shtCopy.Copy after:=wbFinal.Sheets(1)
Set shtPaste = ActiveSheet

End Sub