 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2008memory limit on the number of sheets created
is there a limit on the number of sheets a macro can create? I notice I have a macro that creates sheets and it works up to sheet 26, after sheet 26 it crashes. I read on line that it is limited due to memory? Is that the ram on each computer ? tia, Hi There is no limit imposed by the macro itself. As you have found, the only limit to the number of sheets, is the available memory (RAM) on the system. In most cases you can go way beyond 26 before hitting memory limits. I would think it is something to do with your code. Post the code, and maybe we can help.  SignatureRegards Roger Govier > is there a limit on the number of sheets a macro can create? I notice > I have a macro that creates sheets and it works up to sheet 26, after > sheet 26 it crashes. I read on line that it is limited due to > memory? Is that the ram on each computer ? > > tia, That makes sense, maybe the code is building up an error and just crashes on the 26th line arbitrarily. However, I am getting a little crazy as i've worked on this all day. I just posted it on the excel-programming usenet group. Do you think you could flip over there and give me the answer so I don't offend the list gods? On Jan 21, 5:00 pm, "Roger Govier" <roger@technology4unospamdotcodotuk> wrote: > Hi > [quoted text clipped - 17 lines] > > > tia, Something else is going on. Jan 14th posting from RD states.................. Just as a topic of conversation, since I doubt anyone would go to these lengths: Max sheets is 5,447 As tested by Dana in XL07, And verified by Harlan in XL03. Attempting to insert the 5,448th caused both versions to crash! Gord Dibben MS Excel MVP >is there a limit on the number of sheets a macro can create? I notice >I have a macro that creates sheets and it works up to sheet 26, after >sheet 26 it crashes. I read on line that it is limited due to >memory? Is that the ram on each computer ? > >tia, Helpful posting advice here... http://www.cpearson.com/excel/newposte.htm "Copying Worksheet Programmatically Causes Run-Time Error 1004 in Excel" http://support.microsoft.com/default.aspx?scid=kb;en-us;210684 "XL97: Copy Method of Sheets Object Causes Invalid Page Fault" http://support.microsoft.com/default.aspx?scid=kb;en-us;177634 "XL97: Excel Quits Unexpectedly Running Macro That Creates Chart" http://support.microsoft.com/?kbid=186219 SignatureJim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Excel-General" wrote in message is there a limit on the number of sheets a macro can create? I notice I have a macro that creates sheets and it works up to sheet 26, after sheet 26 it crashes. I read on line that it is limited due to memory? Is that the ram on each computer ? tia, > Helpful posting advice here...http://www.cpearson.com/excel/newposte.htm > > "Copying Worksheet Programmatically Causes Run-Time Error 1004 in Excel"http://support.microsoft.com/default.aspx?scid=kb;en-us;210684 > > "XL97: Copy Method of Sheets Object Causes Invalid Page Fault"http://support.microsoft.com/default.aspx?scid=kb;en-us;177634 I really hate to say this is on a Mac but its on a Mac. However at work I have a PC however I can't test it there. > "XL97: Excel Quits Unexpectedly Running Macro That Creates Chart"http://support.microsoft.com/?kbid=186219 > -- [quoted text clipped - 9 lines] > memory? Is that the ram on each computer ? > tia, Oh I just decided to post it here. If I get an answer here I'll try to advise the other list. It is urgent! Dim lngLastRow As Long Dim ws As Worksheet Dim wb As Workbook Dim c As Range Dim rng As Range Dim sStr As String, Lname As String Dim inputDate As Date Set wb = ThisWorkbook Set ws = wb.Worksheets("patients") Set ws = ThisWorkbook.Worksheets("patients") 'lngLastRow = Cells(Rows.Count, "c").End(xlUp).Row ' lngLastRow = ws.Cells.Find(What:="*", After:=ws.Range("C1"), _ SearchDirection:=xlPrevious).Row lngLastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Debug.Print lngLastRow Set rng = ws.Range("C1:C" & lngLastRow) inputDate = InputBox("Enter a date:", "Date", Date) For Each c In rng.Cells wb.Sheets(2).Copy before:=wb.Sheets(2) Set ws = wb.Sheets(2) ws.Range("T5") = inputDate sStr = c Lname = Mid(sStr, InStr(1, sStr, " ") + 1) ws.Name = Lname Next c End Sub > On Jan 21, 5:02 pm, "Jim Cone" <jim.cone...@rcn.comXXX> wrote:> Helpful posting advice here...http://www.cpearson.com/excel/newposte.htm > [quoted text clipped - 18 lines] > > memory? Is that the ram on each computer ? > > tia, Jim: I read your posts on the overflow error in Excel 2003. I am sure that is the problem After 26 rows it crashes. If I put wb.Save in the for each loop it doesn't crash but it still stops on the 26th row. I even tried saveAs another file after every iteration. it is ugly but it just has to work because you have to keep clicking okay. The problem with that was it saved itself as another file and then I couldn't close it. I could not figure out how to implement Microsoft's workarounds with this. Public Sub cpyAllPatientsShts() Dim lngLastRow As Long Dim ws As Worksheet Dim wb As Workbook Dim c As Range Dim rng As Range Dim sStr As String, Lname As String Dim inputDate As Date Set wb = ThisWorkbook Set ws = wb.Worksheets("patients") Set ws = ThisWorkbook.Worksheets("patients") 'lngLastRow = Cells(Rows.Count, "c").End(xlUp).Row ' lngLastRow = ws.Cells.Find(What:="*", After:=ws.Range("C1"), _ SearchDirection:=xlPrevious).Row lngLastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Set rng = ws.Range("C1:C" & lngLastRow) inputDate = InputBox("Enter a date:", "Date", Date) For Each c In rng.Cells wb.Sheets(2).Copy before:=wb.Sheets(2) Set ws = wb.Sheets(2) ws.Range("T5") = inputDate sStr = c Lname = Mid(sStr, InStr(1, sStr, " ") + 1) ws.Name = Lname Next c End Sub Thanks if you can help. Janis > Helpful posting advice here...http://www.cpearson.com/excel/newposte.htm > [quoted text clipped - 15 lines] > memory? Is that the ram on each computer ? > tia, Maybe there are only 26 cells in the range. Maybe you have the Apple equivalent of Excel 97. You haven't said what Excel version you are using. The MS articles are pretty straight forward. I doubt if I could be any clearer. Of course (always), there could be some other issue afoot. '-- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Excel-General" <jlrough@yahoo.com> wrote in message Jim: I read your posts on the overflow error in Excel 2003. I am sure that is the problem After 26 rows it crashes. If I put wb.Save in the for each loop it doesn't crash but it still stops on the 26th row. I even tried saveAs another file after every iteration. it is ugly but it just has to work because you have to keep clicking okay. The problem with that was it saved itself as another file and then I couldn't close it. I could not figure out how to implement Microsoft's workarounds with this. Public Sub cpyAllPatientsShts() Dim lngLastRow As Long Dim ws As Worksheet Dim wb As Workbook Dim c As Range Dim rng As Range Dim sStr As String, Lname As String Dim inputDate As Date Set wb = ThisWorkbook Set ws = wb.Worksheets("patients") Set ws = ThisWorkbook.Worksheets("patients") 'lngLastRow = Cells(Rows.Count, "c").End(xlUp).Row ' lngLastRow = ws.Cells.Find(What:="*", After:=ws.Range("C1"), _ SearchDirection:=xlPrevious).Row lngLastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Set rng = ws.Range("C1:C" & lngLastRow) inputDate = InputBox("Enter a date:", "Date", Date) For Each c In rng.Cells wb.Sheets(2).Copy before:=wb.Sheets(2) Set ws = wb.Sheets(2) ws.Range("T5") = inputDate sStr = c Lname = Mid(sStr, InStr(1, sStr, " ") + 1) ws.Name = Lname Next c End Sub Thanks if you can help. Janis On Jan 21, 5:02 pm, "Jim Cone" <jim.cone...@rcn.comXXX> wrote: > Helpful posting advice here...http://www.cpearson.com/excel/newposte.htm > [quoted text clipped - 16 lines] > memory? Is that the ram on each computer ? > tia, > Maybe there are only 26 cells in the range. > Maybe you have the Apple equivalent of Excel 97. [quoted text clipped - 64 lines] > > "Copying Worksheet Programmatically Causes Run-Time Error 1004 in > > Excel"http://support.microsoft.com/default.aspx?scid=kb;en-us;210684 It is definitely not the range. I watched the variable output in the immediate window. It is overflowing at row 26. I just need a hack to get it to keep going. > > "XL97: Excel Quits Unexpectedly Running Macro That Creates Chart"http://support.microsoft.com/?kbid=186219 > > -- [quoted text clipped - 9 lines] > > memory? Is that the ram on each computer ? > > tia, Re: "It is definitely not the range. I watched the variable output in the immediate window. It is overflowing at row 26. I just need a hack to get it to keep going." What variable? What do you mean by "overflowing"? I am just guessing, but is the sheet name extracted from cell C longer than the allowed 31 characters. If that is the case just add... On Error Resume Next '<<< ws.Name = Lname On Error GoTo 0 '<<<< SignatureJim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) The extracted range in column C is just a patient Last Name, lname. I only have test data in it and it isn't over 31 characters. The problem is the copy method overflows after 26 lines/ patients. It is Excel 2003. > Re: "It is definitely not the range. I watched the variable output in the > immediate window. It is overflowing at row 26. I just need a hack to [quoted text clipped - 11 lines] > San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware > (Excel Add-ins / Excel Programming) The copy method fails after 26 lines see the comment. The wb.save just saves the object it crashes but it doesn't damage the file as badly. Dim rng As Range Dim sStr As String, Lname As String Dim inputDate As Date Set wb = ThisWorkbook Set ws = wb.Worksheets("patients") Set ws = ThisWorkbook.Worksheets("patients") lngLastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Debug.Print lngLastRow Set rng = ws.Range("C1:C" & lngLastRow) inputDate = InputBox("Enter a date:", "Date", Date) For Each c In rng.Cells wb.Sheets(2).Copy before:=wb.Sheets(2) Set ws = wb.Sheets(2) ws.Range("T5") = inputDate sStr = c Lname = Mid(sStr, InStr(1, sStr, " ") + 1) ws.Name = Lname wb.Save 'need to have save here because of copy method overflow issue in Excel 2003 Next c End Sub > Re: "It is definitely not the range. I watched the variable output in the > immediate window. It is overflowing at row 26. I just need a hack to [quoted text clipped - 11 lines] > San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware > (Excel Add-ins / Excel Programming) I give up. '-- Jim Cone "Excel-General" <jlrough@yahoo.com> wrote in message The copy method fails after 26 lines see the comment. The wb.save just saves the object it crashes but it doesn't damage the file as badly. Dim rng As Range Dim sStr As String, Lname As String Dim inputDate As Date Set wb = ThisWorkbook Set ws = wb.Worksheets("patients") Set ws = ThisWorkbook.Worksheets("patients") lngLastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Debug.Print lngLastRow Set rng = ws.Range("C1:C" & lngLastRow) inputDate = InputBox("Enter a date:", "Date", Date) For Each c In rng.Cells wb.Sheets(2).Copy before:=wb.Sheets(2) Set ws = wb.Sheets(2) ws.Range("T5") = inputDate sStr = c Lname = Mid(sStr, InStr(1, sStr, " ") + 1) ws.Name = Lname wb.Save 'need to have save here because of copy method overflow issue in Excel 2003 Next c End Sub You just don't answer questions of those trying to help you. What is "copy method overflow"? Before you answer, look through your posts again: different code examples, some with, some without declaring variables, no Sub heading, no description of the data in column C, no answer to "is the name longer than 32", etc, etc Please be very precise when you ask others to put effort in helping you BTW if I put short names in Col C, it doesn't stop at row (sheet) 26, it just goes on inserting sheets with the names from Col C. That is, after I dimensioned all variables to what I assume they should be SignatureKind regards, Niek Otten Microsoft MVP - Excel | The copy method fails after 26 lines see the comment. The wb.save | just saves the object it crashes but it doesn't damage the file as [quoted text clipped - 44 lines] | > San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware | > (Excel Add-ins / Excel Programming) The input is coming from the C column range. It is a testing sheet. All the names are Fname1 & " " &Lname1 row 1 Fname2 & "" & Fname2.... row 2. I am not using real data only testing data. So as I said none of the names are over 32 characters. Are you using Excel 2003 on a pc? I wish I could try it on a pc. The copy method overflow is listed as a 1004 error which it hits when the application is out of memory. I will double check the dimensions of the variables. Are you talking about the very last posted one? I will try it again and get back to you. > You just don't answer questions of those trying to help you. > What is "copy method overflow"? [quoted text clipped - 60 lines] > | > San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware > | > (Excel Add-ins / Excel Programming) Okay, the error I get is runtime error 1004, copy method of worksheet failed. Here is the code. I don't see anything that isn't dimensioned. Public Sub cpyAllPatientsShts() Dim lngLastRow As Long Dim ws As Worksheet Dim wb As Workbook Dim c As Range Dim rng As Range Dim sStr As String Dim Lname As String Dim inputDate As String Set wb = ThisWorkbook Set ws = wb.Worksheets("patients") Set ws = ThisWorkbook.Worksheets("patients") 'lngLastRow = Cells(Rows.Count, "c").End(xlUp).Row ' lngLastRow = ws.Cells.Find(What:="*", After:=ws.Range("C1"), _ SearchDirection:=xlPrevious).Row lngLastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Debug.Print lngLastRow Set rng = ws.Range("C1:C" & lngLastRow) inputDate = InputBox("Enter a date:", "Date", Date, 100, 100) For Each c In rng.Cells wb.Sheets(2).Copy before:=wb.Sheets(2) wb.Save Set ws = wb.Sheets(2) ws.Range("T5") = inputDate sStr = c Lname = Mid(sStr, InStr(1, sStr, " ") + 1) ws.Name = Lname Next c End Sub > You just don't answer questions of those trying to help you. > What is "copy method overflow"? [quoted text clipped - 5 lines] > BTW if I put short names in Col C, it doesn't stop at row (sheet) 26, it just goes on inserting sheets with the names from Col C. > That is, after I dimensioned all variables to what I assume they should be thanks in advance I see the dimension problem, sorry, > Okay, the error I get is runtime error 1004, copy method of worksheet > failed. [quoted text clipped - 48 lines] > > thanks in advance I found one dimension problem which I fixed and i still get the 1004 runtime error. Can you see any other dimension problem? I would like to establish it is a memory problem. thanks so much for your help. Public Sub cpyAllPatientsShts() Dim lngLastRow As Long Dim ws As Worksheet Dim wb As Workbook Dim c As Range Dim rng As Range Dim sStr As String, Lname As String Dim inputDate As String Set wb = ThisWorkbook Set ws = wb.Worksheets("patients") 'lngLastRow = Cells(Rows.Count, "c").End(xlUp).Row ' lngLastRow = ws.Cells.Find(What:="*", After:=ws.Range("C1"), _ SearchDirection:=xlPrevious).Row lngLastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Debug.Print lngLastRow Set rng = ws.Range("C1:C" & lngLastRow) inputDate = InputBox("Enter a date:", "Date", Date, 100, 100) For Each c In rng.Cells wb.Sheets(2).Copy before:=wb.Sheets(2) wb.Save Set ws = wb.Sheets(2) ws.Range("T5") = inputDate sStr = c Lname = Mid(sStr, InStr(1, sStr, " ") + 1) ws.Name = Lname Next c End Sub Worked absolutely fine for me (without the Save), in XL2003 on Vista Business. SignatureRegards Roger Govier > I found one dimension problem which I fixed and i still get the 1004 > runtime error. Can you see any other dimension problem? I would like [quoted text clipped - 37 lines] > Next c > End Sub It definitely didn't on my excel2003 mac at all ever, but I could test the whole thing on my pc tomorrow at work. Thanks for checking. It was stopping on the line before next c where it assigns the name of the sheet. I am wondering if it had something to do with the form or something hidden? I don't think so though, The interesting thing is the user slightly changed what they wanted. I use the same loop and same variables I just print after I copy and I delete the sheet after I print it instead of saving all of them. It works knock on wood. Maybe that proves it was a memory problem although it copies all of them just the same it just deletes them during each iteration. Wow it was like one thing worked today 24 hours to late but it worked. :-) On Jan 22, 5:20 pm, "Roger Govier" <roger@technology4unospamdotcodotuk> wrote: > Worked absolutely fine for me (without the Save), in XL2003 on Vista > Business. [quoted text clipped - 44 lines] > > > Next c How about to try adding a sheet and copy all cells to the added sheet insted of copying a sheet. I tried to modify your code according to above, though i don't know it work or not in your case. Sub ModifycpyAllPatientsShts() Dim lngLastRow As Long Dim ws As Worksheet Dim wb As Workbook Dim c As Range Dim rng As Range Dim sStr As String, Lname As String Dim inputDate As Date Dim p Set wb = ThisWorkbook Set ws = wb.Worksheets("patients") 'lngLastRow = Cells(Rows.Count, "c").End(xlUp).Row ' lngLastRow = ws.Cells.Find(What:="*", After:=ws.Range("C1"), _ SearchDirection:=xlPrevious).Row lngLastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row Set rng = ws.Range("C1:C" & lngLastRow) Debug.Print lngLastRow inputDate = InputBox("Enter a date:", "Date", Date) On Error GoTo errhandle wb.Sheets(2).Copy before:=wb.Sheets(2) For Each c In rng.Cells errcount = 0 wb.Worksheets.Add before:=Sheets(2) wb.Sheets(3).Cells.Copy Destination:=Sheets(2).Range("a1") Set ws = wb.Sheets(2) ws.Range("T5") = inputDate sStr = c Lname = Mid(sStr, InStr(1, sStr, " ") + 1) 'check valid sheet name and length For Each p In Array("[", "]", ":", "*", "/", "?", _ Application.PathSeparator) If InStr(Lname, p) > 0 Then Lname = Replace(Lname, p, "_") End If Next If Len(Lname) > 31 Then Lname = Left(Lname, 31) End If ws.Name = Lname Next c Exit Sub errhandle: If Err.Number = 1004 Then p = Split(Lname, " -") If UBound(p) > 0 Then If Len(p(0)) + Len(p(1) + 1) + 2 > 31 Then p(0) = Left(p(0), 31 - Len(p(1)) - 3) Lname = Left(p(0), 31 - Len(p(1)) - 3) _ & Space(1) & "-" & Trim(Val(p(1)) + 1) Else Lname = p(0) & Space(1) & "-" & Trim(Val(p(1)) + 1) End If ElseIf Len(Lname) = 31 Then Lname = Left(Lname, 31 - 3) & Space(1) & "-1" Else Lname = Lname & Space(1) & "-1" End If Else MsgBox "Unexpected error occured" Exit Sub End If Resume End Sub keiji > Okay, the error I get is runtime error 1004, copy method of worksheet > failed. > Here is the code. I don't see anything that isn't dimensioned. > Public Sub cpyAllPatientsShts() -snip- > How about to try adding asheetandcopyall cells to the addedsheet > insted of copying asheet. [quoted text clipped - 83 lines] > > -snip- Back again after a while........... The REAL issue here is NOT that there is a limit to the number of sheets that can be CREATED with a macro. It is INSTEAD, and MORE importantly the number of times a sheet can be copied WITHIN a file by a macro.!! I hit the same block, read the link to the MS issue, Accepted this as a REAL limit and built a very simple and effective work around. I have a two-sheet file with the sheet I wish to copy as one. When I need a new copy of the sheet, I (in code), open the file, copy the sheet into my specified location, these close that "Slave" file. Works like a dream! Accept that there really are limits, and unless you have LOTS of spare time, build a workaround!! Particularly when you are TOLD by Bill's slaves that this is an acknowledged issue.
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.