Paste method of Worksheet class failed

[tschop]

I have some code that creates a new worksheet and renames it. The code works fine unless someone runs it a second time in which case the name already exists. To solve this problem, I added an error handling routine that deletes the extra sheet and skips the naming statement. When the error handling routine is invoked, I get the "Paste method of Worksheet class failed" error, if the error routine is not invoked, it works fine. Any thoughts would be appreciated.

 If efgpropexist = True And ncspropexist = False Then
            Sheets.Add After:=Sheets(Sheets.count)
            On Error GoTo ErrorHandler
            Sheets(Sheets.count).Name = "EFGSum Prop" & k
            On Error GoTo 0
            Application.ScreenUpdating = False
            Application.CutCopyMode = xlCopy
            Sheets("Summary").Select
            Cells.Select
            Application.CutCopyMode = False
            Selection.Copy
            ThisWorkbook.Worksheets("EFGSum Prop" & k).Activate
            ThisWorkbook.Worksheets("EFGSum Prop" & k).Range("A1").Select
            Application.CutCopyMode = False
            ThisWorkbook.Worksheets("EFGSum Prop" & k).Paste
            ThisWorkbook.Worksheets("EFG Proposal " & k).Range("EFGFabMat").Formula = "=" & Chr(39) & "EFGSum Prop" & k & Chr(39) & "!C60"
            ThisWorkbook.Worksheets("EFG Proposal " & k).Range("EFGInstall").Formula = "=" & Chr(39) & "EFGSum Prop" & k & Chr(39) & "!C59"
            ThisWorkbook.Worksheets("EFG Proposal " & k).Range("EFGTravel").Formula = "=" & Chr(39) & "EFGSum Prop" & k & Chr(39) & "!C64" 
            If ThisWorkbook.Worksheets("EFG Proposal " & k).Range("EFGTaxrate").value <> 0 Then
                ThisWorkbook.Worksheets("EFG Proposal " & k).Range("EFGTaxrate").Formula = "=" & Chr(39) & "EFGSum Prop" & k & Chr(39) & "!C62"
            Else
                ThisWorkbook.Worksheets("EFG Proposal " & k).Range("EFGTaxrate").value = 0
            End If
End If

The error handling routine is

Exit Sub
ErrorHandler:
    Application.DisplayAlerts = False
    Sheets(Sheets.count).Delete
    Application.DisplayAlerts = True
    Resume

End Sub

 

[tschop]

Okay,

I feel like a dummy. I just noticed the "Application.CutCopyMode = False" statement, when I commented it out the error went away.

Sorry for the bother.

Tchop

 

[mumps]

This macro only adds the sheet if it doesn't exist. I tidied up the code a bit.

Sub Test()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    If efgpropexist = True And ncspropexist = False Then
        Set ws = Nothing
        On Error Resume Next
        Set ws = Sheets("EFGSum Prop" & k)
        On Error GoTo 0
        If ws Is Nothing Then
            Sheets.Add After:=Sheets(Sheets.Count).Name = "EFGSum Prop" & k
            Sheets("Summary").Cells.Copy Sheets("EFGSum Prop" & k).Range("A1")
            With Sheets("EFG Proposal " & k)
                .Range("EFGFabMat").Formula = "=" & Chr(39) & "EFGSum Prop" & k & Chr(39) & "!C60"
                .Range("EFGInstall").Formula = "=" & Chr(39) & "EFGSum Prop" & k & Chr(39) & "!C59"
                .Range("EFGTravel").Formula = "=" & Chr(39) & "EFGSum Prop" & k & Chr(39) & "!C64"
                If .Range("EFGTaxrate").Value <> 0 Then
                    .Range("EFGTaxrate").Formula = "=" & Chr(39) & "EFGSum Prop" & k & Chr(39) & "!C62"
                Else
                    .Range("EFGTaxrate").Value = 0
                End If
            End With
        End If
    End If
    Application.ScreenUpdating = True
End Sub

You have to incorporate the code within any existing code.