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?