Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cannot close a workbook in VBA due to Run-Time error '1004' | Excel Programming | |||
Copy paste to another Workbook, Error 1004 | Excel Programming | |||
Opening a workbook, passwords and error 1004. | Excel Programming | |||
workbook.open throws 1004 error | Excel Programming | |||
shared workbook runtime error 1004 | Excel Programming |