Hi.
I have a XL workbook with 3 sheets. When ever the user needs an addtional sheet, the code (below) adds a sheet and copies the contents of the sheet 3.

It needs to add one sheet at a time after clicking a "macro button". Here is the probelm: I can only add up to 37 sheets. After 37, I get the above error message. Can you suggest a modification to this code so that I can add up to 60 sheets.

Thanks.

Kolar

Sub AddClaimForm()
Application.ScreenUpdating = False
If strMasterPassword = "" Then
strMasterPassword = Worksheets(1).Range("E100").Value
End If
Sheets("Cover page").Unprotect Password:=strMasterPassword
ActiveWorkbook.Unprotect Password:=strMasterPassword
'I get the run-time error at the line below
Sheets(Worksheets.Count).Copy After:=Sheets(Worksheets.Count)
Sheets(Worksheets.Count).Name = "Claim " & Worksheets.Count - 2

Sheets(Worksheets.Count).Activate
Sheets(Worksheets.Count).Unprotect Password:=strMasterPassword
Cells.Select
Selection.Locked = True
Selection.FormulaHidden = True
Range("F3:G3,J3:K3,C5,F5,K5,d27:d38,F39,f41,F43:F4 4,F45,F48,F50:F53,e72,i64,i67,i71,i73").Select
Selection.ClearContents
Selection.FormulaHidden = False
Selection.Locked = False
Range("B27:C38,E27:E38,B43,B44,B48,B50,e69,I75").S elect
Selection.Locked = False
Selection.FormulaHidden = False

' Update Formula's
Cells.Replace What:="'Claim " & Worksheets.Count - 4 & "'", Replacement:="'Claim " & Worksheets.Count - 3 & "'", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False

Sheets("Claim " & Worksheets.Count - 2).Protect Password:=strMasterPassword
Range("F3").Select

Sheets(Worksheets.Count).Protect Password:=strMasterPassword
Sheets("Cover page").Protect Password:=strMasterPassword
ActiveWorkbook.Protect Password:=strMasterPassword
Application.ScreenUpdating = True
End Sub

Hi,

According to worksheet specifications in Excel (go to help and type in "worksheet specifications"), the number of sheets you can add are limited by available memory. I'm not certain that's what you're running into, see if you can get on another machine with more memory to eliminate this possiblity.

Hope this helps,
Mike

Welcome to the Board!

I agree with Mike, in that it sounds like you're maxing your PC.

To test it, try:Use CTRL+Break to end it.

Also regarding your code, note that With statements will speed up its execution as will getting rid of the Select statements. You generally do not need to select objects to work with them.Hope that helps,

Smitty

Hi Mike and Smitty:

Thanks a lot.

I will try your recommendations and let you know the end result.
Further to my previous post: The user do not need to add the additional sheets all at once. I think the error will occur only if one tries to add all the shets at once before sving the file in between the new sheets.

Nevertheless, I will test your suggestions.


I appreciate Smitty's suggesiton to use "with". I am all for making the code more efficient and elegant. I am a novice in programming, so am learning.

Cheers
Kolar

Hi Smitty:
I tried your code with "With", however, I get an error " 1004 can't change part of a merged cell". My previous code works Ok! Is there something msissing?

Kolar

As a side note about merged cells: Unless it is absolutely, positively, absolutely, absolutely necessary for merged cells, on threat of severe physical punishment, merged cells should be avoided in spreadsheet design. They make formatting "look nice" but can cause problems in general.

As an alternative, try selecting all the cells, right click and choose Format, then Alignment, in the Text Alignment section for Horizontal dropdown list, choose Center Across Selection. Gives the same effect, without the negatives of merged cells.