Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    Canada
    Posts
    3

    Unanswered: Runtime Error-1004 "Copy method of worksheet calss failed"

    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

  2. #2
    Join Date
    Mar 2004
    Location
    Minnesota, USA, Earth
    Posts
    65

    Re: Runtime Error-1004 "Copy method of worksheet calss failed"

    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


    Originally posted by Kolar
    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

  3. #3
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    Welcome to the Board!

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

    To test it, try:
    Code:
    Sub AddSheets()
        Do
            Worksheets.Add
        Loop
    End Sub
    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.
    Code:
    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
        
        With Sheets(Worksheets.Count)
            .Copy After:=Sheets(Worksheets.Count)
            .Name = "Claim " & Worksheets.Count - 2
            .Activate
            .Unprotect Password:=strMasterPassword
        End With
        
        With Cells
            .Locked = True
            .FormulaHidden = True
        End With
        
        With Range(" F3:G3,J3:K3,C5,F5,K5,d27:d38,F39,f41,F43:F44,F45,F48,F50:F53,e72,i64,i67,i71,i73")
            .ClearContents
            .FormulaHidden = False
            .Locked = False
        End With
                
        With Range("B27:C38,E27:E38,B43,B44,B48,B50,e69,I75")
            .Locked = False
            .FormulaHidden = False
        End With
        
        ' 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
    Hope that helps,

    Smitty

  4. #4
    Join Date
    Feb 2004
    Location
    Canada
    Posts
    3
    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

  5. #5
    Join Date
    Feb 2004
    Location
    Canada
    Posts
    3
    Quote Originally Posted by Smitty
    Welcome to the Board!

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

    To test it, try:
    Code:
    Sub AddSheets()
        Do
            Worksheets.Add
        Loop
    End Sub
    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.
    Code:
    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
        
        With Sheets(Worksheets.Count)
            .Copy After:=Sheets(Worksheets.Count)
            .Name = "Claim " & Worksheets.Count - 2
            .Activate
            .Unprotect Password:=strMasterPassword
        End With
        
        With Cells
            .Locked = True
            .FormulaHidden = True
        End With
        
        With Range(" F3:G3,J3:K3,C5,F5,K5,d27:d38,F39,f41,F43:F44,F45,F48,F50:F53,e72,i64,i67,i71,i73")
            .ClearContents
            .FormulaHidden = False
            .Locked = False
        End With
                
        With Range("B27:C38,E27:E38,B43,B44,B48,B50,e69,I75")
            .Locked = False
            .FormulaHidden = False
        End With
        
        ' 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
    Hope that helps,

    Smitty

    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

  6. #6
    Join Date
    Oct 2003
    Posts
    1,091
    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.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •