| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with creating Worksheets with Macros
I have a macro that depending on the options created by the user will create
additional worksheets. Up to 70 worksheets could be needed in the file. The marco will only create 62 worksheets. It crashes when it attempts to create the 63 worksheet. After it crashes I cannot manually add any worksheets to the file. Nothing happens when I try to insert or copy a worksheet. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with creating Worksheets with Macros
According to the Excel specifications, the number of sheets is limited only
by available memory. If you have a set with diminished memory, that could be the problem. "Jim" wrote in message ... I have a macro that depending on the options created by the user will create additional worksheets. Up to 70 worksheets could be needed in the file. The marco will only create 62 worksheets. It crashes when it attempts to create the 63 worksheet. After it crashes I cannot manually add any worksheets to the file. Nothing happens when I try to insert or copy a worksheet. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with creating Worksheets with Macros
Which Excel version do you use ?
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jim" wrote in message ... I have a macro that depending on the options created by the user will create additional worksheets. Up to 70 worksheets could be needed in the file. The marco will only create 62 worksheets. It crashes when it attempts to create the 63 worksheet. After it crashes I cannot manually add any worksheets to the file. Nothing happens when I try to insert or copy a worksheet. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with creating Worksheets with Macros
Excel 2003
"Ron de Bruin" wrote: Which Excel version do you use ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jim" wrote in message ... I have a macro that depending on the options created by the user will create additional worksheets. Up to 70 worksheets could be needed in the file. The marco will only create 62 worksheets. It crashes when it attempts to create the 63 worksheet. After it crashes I cannot manually add any worksheets to the file. Nothing happens when I try to insert or copy a worksheet. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with creating Worksheets with Macros
I have 4 gb of memory. I don't believe it is a memory issue since I don't
have any problems creating/adding worksheets manually. The problem only occurs when I use a macro. "JLGWhiz" wrote: According to the Excel specifications, the number of sheets is limited only by available memory. If you have a set with diminished memory, that could be the problem. "Jim" wrote in message ... I have a macro that depending on the options created by the user will create additional worksheets. Up to 70 worksheets could be needed in the file. The marco will only create 62 worksheets. It crashes when it attempts to create the 63 worksheet. After it crashes I cannot manually add any worksheets to the file. Nothing happens when I try to insert or copy a worksheet. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with creating Worksheets with Macros
In Excel 97 there was a problem but not in 2003
What is the code doing ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jim" wrote in message ... Excel 2003 "Ron de Bruin" wrote: Which Excel version do you use ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jim" wrote in message ... I have a macro that depending on the options created by the user will create additional worksheets. Up to 70 worksheets could be needed in the file. The marco will only create 62 worksheets. It crashes when it attempts to create the 63 worksheet. After it crashes I cannot manually add any worksheets to the file. Nothing happens when I try to insert or copy a worksheet. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with creating Worksheets with Macros
Then the problem could be in how the code is constructed. It might be
caching something in an iterative loop that would exceed a limit. Depending on what and how would determine if you get an error message. Sometimes setting the object variables to nothing after a certain number of iterations will help. "Jim" wrote in message ... I have 4 gb of memory. I don't believe it is a memory issue since I don't have any problems creating/adding worksheets manually. The problem only occurs when I use a macro. "JLGWhiz" wrote: According to the Excel specifications, the number of sheets is limited only by available memory. If you have a set with diminished memory, that could be the problem. "Jim" wrote in message ... I have a macro that depending on the options created by the user will create additional worksheets. Up to 70 worksheets could be needed in the file. The marco will only create 62 worksheets. It crashes when it attempts to create the 63 worksheet. After it crashes I cannot manually add any worksheets to the file. Nothing happens when I try to insert or copy a worksheet. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with creating Worksheets with Macros
Depending on options selected by the user the code copies an existing
worksheet to a new worksheet and then other code updates the equations on the new worksheet. The code that I am using to copy the exisiting worksheet is: Sheets("GWMODEL").Select Sheets("GWMODEL").Copy Befo=Sheets("SRMODEL") Sheets("GWMODEL (2)").Select Sheets("GWMODEL (2)").Name = SheetName My trial and error I discovered the problem was occuring after the program had created copies of 32 worksheets. It did not matter how many worksheets were in the workbook. When the program tried to create a 33rd it would crash. I also discovered that while I could not then manaully copy any worksheet, that I could insert a blank worksheet and copy the contents from an exisiting worksheet to the blank worksheet, thereby creating a new copy of the worksheet. So instead of having the code copy the worksheets, I changed the code to insert a blank worksheet and then copy the contents from an existng worksheet to the blank worksheet and now the program runs without any problems. "Ron de Bruin" wrote: In Excel 97 there was a problem but not in 2003 What is the code doing ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jim" wrote in message ... Excel 2003 "Ron de Bruin" wrote: Which Excel version do you use ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jim" wrote in message ... I have a macro that depending on the options created by the user will create additional worksheets. Up to 70 worksheets could be needed in the file. The marco will only create 62 worksheets. It crashes when it attempts to create the 63 worksheet. After it crashes I cannot manually add any worksheets to the file. Nothing happens when I try to insert or copy a worksheet. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with creating Worksheets with Macros
Hi Jim
See also this KB http://support.microsoft.com/default...b;en-us;210684 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jim" wrote in message ... Depending on options selected by the user the code copies an existing worksheet to a new worksheet and then other code updates the equations on the new worksheet. The code that I am using to copy the exisiting worksheet is: Sheets("GWMODEL").Select Sheets("GWMODEL").Copy Befo=Sheets("SRMODEL") Sheets("GWMODEL (2)").Select Sheets("GWMODEL (2)").Name = SheetName My trial and error I discovered the problem was occuring after the program had created copies of 32 worksheets. It did not matter how many worksheets were in the workbook. When the program tried to create a 33rd it would crash. I also discovered that while I could not then manaully copy any worksheet, that I could insert a blank worksheet and copy the contents from an exisiting worksheet to the blank worksheet, thereby creating a new copy of the worksheet. So instead of having the code copy the worksheets, I changed the code to insert a blank worksheet and then copy the contents from an existng worksheet to the blank worksheet and now the program runs without any problems. "Ron de Bruin" wrote: In Excel 97 there was a problem but not in 2003 What is the code doing ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jim" wrote in message ... Excel 2003 "Ron de Bruin" wrote: Which Excel version do you use ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jim" wrote in message ... I have a macro that depending on the options created by the user will create additional worksheets. Up to 70 worksheets could be needed in the file. The marco will only create 62 worksheets. It crashes when it attempts to create the 63 worksheet. After it crashes I cannot manually add any worksheets to the file. Nothing happens when I try to insert or copy a worksheet. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Creating new worksheets and appending data from multiple worksheets. | Excel Programming | |||
| VBA / Macro for creating new worksheets and new columns from existing worksheets | Excel Programming | |||
| Creating Pivot Tables with Macros Problem | Excel Programming | |||
| creating macros in multiple worksheets | Excel Worksheet Functions | |||
| Sort Numerically Worksheets via VB when creating a new worksheets | Excel Programming | |||
Linear Mode
