memory limit on the number of sheets created

E

Excel-General

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,
 
R

Roger Govier

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.
 
G

Gord Dibben

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
 
J

Jim Cone

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
--
Jim 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,
 
E

Excel-General

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?
 
E

Excel-General

E

Excel-General

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
 
E

Excel-General

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
 
J

Jim Cone

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"
<[email protected]>
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
 
E

Excel-General

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, USAhttp://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"Excel-General"
<[email protected]>
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
Click to expand...
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.
 
J

Jim Cone

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 '<<<<
 
E

Excel-General

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.
 
E

Excel-General

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
 
J

Jim Cone

I give up.
'--
Jim Cone


"Excel-General"
<[email protected]>
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
 
N

Niek Otten

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

--
Kind 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
| 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
| > 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 '<<<<
| > --
| > Jim Cone
| > San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware
| > (Excel Add-ins / Excel Programming)
 
E

Excel-General

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.
 
E

Excel-General

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"?
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
Click to expand...
thanks in advance
 
E

Excel-General

I see the dimension problem, sorry,


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





thanks in advance
Click to expand...
 
E

Excel-General

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Arrays containing variable sheets 2
Quick/efficient way to VBA subtotal 2
Excel Monthly Spread sheet. 12
Macro to copy portion of each sheet and paste into the first sheet asa picture 1
Limiing extent of printout 3
More Rows/One Sheet vs More Sheets/Less Rows for best performance? 1
Creating Multiple charts in an Excel Sheet :Post 2 2
Count the columns from one sheet and paste it in another sheet 0

Top