I have a weekly programming programme in excel that adds a new worksheet for
each week. Unfortunately it only works up to 44weeks and then hits a runtime
error. On another computer it only gets to 43weeks. Reading Excel help I
understand the number of worksheets is limited by memory.

Is there any chance that this can be overcome to allow more sheets to be
added?

44 sheets doesn't sound like it would consume a lot of RAM. How much memory
do you have on your machine?

Dave
--
Brevity is the soul of wit.


"Bug289" wrote:

I have a weekly programming programme in excel that adds a new worksheet for
each week. Unfortunately it only works up to 44weeks and then hits a runtime
error. On another computer it only gets to 43weeks. Reading Excel help I
understand the number of worksheets is limited by memory.

Is there any chance that this can be overcome to allow more sheets to be
added?

You don't tell much about your hardware or your layout with, as yet,
unneeded formulas or formatting. Also, unless you are going to exceed the
available rows it it NOT necessary to have a worksheet for each week. Put
all on ONE and then use filters or formulas to tell you about each week.

--
Don Guillett
SalesAid Software

"Bug289" wrote in message
...
I have a weekly programming programme in excel that adds a new worksheet
for
each week. Unfortunately it only works up to 44weeks and then hits a
runtime
error. On another computer it only gets to 43weeks. Reading Excel help I
understand the number of worksheets is limited by memory.

Is there any chance that this can be overcome to allow more sheets to be
added?

How are you adding the sheet? There is a resource problem with Sheets.Copy
in VBA.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Bug289" wrote in message
...
I have a weekly programming programme in excel that adds a new worksheet
for
each week. Unfortunately it only works up to 44weeks and then hits a
runtime
error. On another computer it only gets to 43weeks. Reading Excel help I
understand the number of worksheets is limited by memory.

Is there any chance that this can be overcome to allow more sheets to be
added?

I just used this with NO problem

Sub mynewsheets()
For i = 1 To 200
On Error Resume Next
Sheets.Add.Name = i
Next i
End Sub

--
Don Guillett
SalesAid Software

"Bug289" wrote in message
...
I have a weekly programming programme in excel that adds a new worksheet
for
each week. Unfortunately it only works up to 44weeks and then hits a
runtime
error. On another computer it only gets to 43weeks. Reading Excel help I
understand the number of worksheets is limited by memory.

Is there any chance that this can be overcome to allow more sheets to be
added?

This is the main module, the rest seem to be mostly setting formatting.

Sub Add_week()
'
' Add_week Macro
' Add weekly programme sheet
'
' Keyboard Shortcut: Ctrl+w
'
ActiveSheet.Unprotect
Sheets("Contract").Select
strReturnValue1 = Range("A2")
strReturnValue2 = Range("B1")
strReturnValue3 = Range("a3")
strReturnValue4 = Range("b3")
Sheets("Blank").Select
Sheets("Blank").Copy Befo=Sheets("end")
Sheets("Blank (2)").Select
Sheets("Blank (2)").Name = strReturnValue1
ActiveSheet.Unprotect
Range("C9:I54").Select
Selection.ClearContents
Range("D9:I54").Select
Application.Run "'Weekly Programme_01.xls'!unfill"
Range("K9:O54").Select
Application.Run "'Weekly Programme_01.xls'!unfill"
ActiveSheet.Unprotect
Range("P9:P54").Select
Selection.ClearContents
Range("c2").Select
ActiveCell.FormulaR1C1 = strReturnValue3
Sheets("Contract").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = strReturnValue2
Range("A3").Select
ActiveCell.FormulaR1C1 = strReturnValue4
Range("C1").Select
Selection.Locked = False
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

When I debug it stops at this line:
Sheets("Blank").Copy Befo=Sheets("end")
if that helps.

thanks

"Jon Peltier" wrote:

How are you adding the sheet? There is a resource problem with Sheets.Copy
in VBA.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Bug289" wrote in message
...
I have a weekly programming programme in excel that adds a new worksheet
for
each week. Unfortunately it only works up to 44weeks and then hits a
runtime
error. On another computer it only gets to 43weeks. Reading Excel help I
understand the number of worksheets is limited by memory.

Is there any chance that this can be overcome to allow more sheets to be
added?

This is the kind of syntax that would lead to a problem in my scenario. The
more intricate the sheet (and the less system RAM) the sooner it would crap
out.

Is the "Blank" sheet really blank? Just use Sheets.Add

Is the "Blank" sheet something you could change to a template on its own?
Then use Sheets.Add(Type:=PathAndFileName) where PathAndFileName is the full
name of the template.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Bug289" wrote in message
...
This is the main module, the rest seem to be mostly setting formatting.

Sub Add_week()
'
' Add_week Macro
' Add weekly programme sheet
'
' Keyboard Shortcut: Ctrl+w
'
ActiveSheet.Unprotect
Sheets("Contract").Select
strReturnValue1 = Range("A2")
strReturnValue2 = Range("B1")
strReturnValue3 = Range("a3")
strReturnValue4 = Range("b3")
Sheets("Blank").Select
Sheets("Blank").Copy Befo=Sheets("end")
Sheets("Blank (2)").Select
Sheets("Blank (2)").Name = strReturnValue1
ActiveSheet.Unprotect
Range("C9:I54").Select
Selection.ClearContents
Range("D9:I54").Select
Application.Run "'Weekly Programme_01.xls'!unfill"
Range("K9:O54").Select
Application.Run "'Weekly Programme_01.xls'!unfill"
ActiveSheet.Unprotect
Range("P9:P54").Select
Selection.ClearContents
Range("c2").Select
ActiveCell.FormulaR1C1 = strReturnValue3
Sheets("Contract").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = strReturnValue2
Range("A3").Select
ActiveCell.FormulaR1C1 = strReturnValue4
Range("C1").Select
Selection.Locked = False
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
End Sub

When I debug it stops at this line:
Sheets("Blank").Copy Befo=Sheets("end")
if that helps.

thanks

"Jon Peltier" wrote:

How are you adding the sheet? There is a resource problem with
Sheets.Copy
in VBA.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Bug289" wrote in message
...
I have a weekly programming programme in excel that adds a new worksheet
for
each week. Unfortunately it only works up to 44weeks and then hits a
runtime
error. On another computer it only gets to 43weeks. Reading Excel
help I
understand the number of worksheets is limited by memory.

Is there any chance that this can be overcome to allow more sheets to
be
added?