Copying sheet with relative formula named range causes errors.

Hello!

I use this copy sheet code and it works great. Occasionally, though, I get an error popping up: "A formula or sheet you want to move or copy contains the name 'ErrorCheck', which already exists on the destination sheet. Do you want to use this version of the name?"

If I click Yes, then the named range ErrorCheck is used, but it is the sheet ErrorCheck rather than the workbook-wide ErrorCheck.

Anyone have any ideas how to eliminate this error?

Private Sub cmbCopySheets_Click()
    Dim c01 As String
    Dim ws As Worksheet
    Dim Ans As VbMsgBoxResult
    On Error GoTo Errhandler
NAME_AGAIN:
    c01 = InputBox("Please enter new sheet name.")
    If c01 = "" Then Exit Sub
    If SheetExists(c01) Then
            MsgBox "Sheet " & c01 & " already exists.", vbExclamation, "Sheet Error"
            GoTo NAME_AGAIN
    ElseIf Not IsValidSheetName(c01) Then
            MsgBox "Sheet name " & c01 & " is invalid (is blank, too long, or contains reserved characters (/ \ : | ? *).", vbExclamation, "Sheet Error"
            GoTo NAME_AGAIN
    ElseIf Err.Number <> 0 Then
            MsgBox c01 & " has a Sheet Error: " & Err.Description, vbExclamation, "Sheet Error"
            Exit Sub
    End If
        Application.ScreenUpdating = False
        ActiveWorkbook.Unprotect "SECRET"
        ActiveSheet.Copy , ActiveSheet
        With ActiveSheet
            .name = c01
            '.Unprotect "SECRET"
            '.OLEObjects("cmbCopySheets").Visible = msoFalse
            '.Protect "SECRET"
        End With
        ActiveWorkbook.Protect "SECRET"
        Application.ScreenUpdating = True
Exit Sub
Errhandler:

      MsgBox Err.Description & vbCrLf & vbCrLf & "Not executing Copy Sheet request."
      Err.Clear
End Sub

Function SheetExists(SHname As String, _
                     Optional wkb As Workbook = Nothing) As Boolean
    On Error Resume Next
    SheetExists = Not IIf(wkb Is Nothing, ActiveWorkbook, wkb).Sheets(SHname) Is Nothing
    If Err.Number Then Err.Clear
End Function

Function IsValidSheetName(s As String) As Boolean
    If Len(s) = 0 Or Len(s) > 31 Then Exit Function
    If InStr(s, "\") Then Exit Function
    If InStr(s, "/") Then Exit Function
    If InStr(s, ":") Then Exit Function
    If InStr(s, "|") Then Exit Function
    If InStr(s, "*") Then Exit Function
    If InStr(s, "?") Then Exit Function
    IsValidSheetName = True
End Function

Named Ranges:

ErrorCheck: =Cover!$E3=INDEX(Customers, MATCH(Cover!$E4, Plants, 0))

MyPlants:=IF(TRIM(Cover!$E3)="",Message, INDEX(Plants, MATCH(Cover!$E3, Customers, 0)) : INDEX(Plants, MATCH(Cover!$E3 & "zzz", Customers)))

Thanks!

Lost

Jerry,

The error pops up sporadically, so hopefully you see it:

1) Go to one of the green sheet tabs.
2) Click the Copy Sheet button.
3) Name the copied sheet something that starts with a number ("0050", etc.) (I don't know if using a number as the sheet name causes the error, but that is what we name our sheets. Maybe something else will make it pop up.)
4) If all goes well, you'll see the error described above.
5) Otherwise, the sheet will copy just fine with no error.

Thanks for looking into this!

Lost

Jerry,

I tried to apply your code to my code, but I don't know what I am doing.

This isn't right:

ActiveSheet.Copy , ActiveSheet
        ActiveSheet(L1.Text).name = c01 & ""

Can you help?

Respectfully,

Lost

Jerry,

I updated the SHEETEXISTS function with what you supplied, but I don't know where in this code I need to put your code from Post #4.

Here is what I have, but it needs your #4 post code:

Option Explicit
Private Sub cmbCopySheets_Click()
    Dim c01 As String
    Dim ws As Worksheet
    Dim Ans As VbMsgBoxResult
    On Error GoTo Errhandler
NAME_AGAIN:
    c01 = InputBox("Please enter new sheet name.")
    If c01 = "" Then Exit Sub
    If SheetExists(c01) Then
            MsgBox "Sheet " & c01 & " already exists.", vbExclamation, "Sheet Error"
            GoTo NAME_AGAIN
    ElseIf Not IsValidSheetName(c01) Then
            MsgBox "Sheet name " & c01 & " is invalid (is blank, too long, or contains reserved characters (/ \ : | ? *).", vbExclamation, "Sheet Error"
            GoTo NAME_AGAIN
    ElseIf Err.Number <> 0 Then
            MsgBox c01 & " has a Sheet Error: " & Err.Description, vbExclamation, "Sheet Error"
            Exit Sub
    End If
        Application.ScreenUpdating = False
        ActiveWorkbook.Unprotect "SECRET"
        ActiveSheet.Copy , ActiveSheet
        With ActiveSheet
            .name = c01
            End With
        ActiveWorkbook.Protect "SECRET"
        Application.ScreenUpdating = True
Exit Sub
Errhandler:

      MsgBox Err.Description & vbCrLf & vbCrLf & "Not executing Copy Sheet request."
      Err.Clear
End Sub

Function SheetExists(SHname As String) As Boolean
    SheetExists = Evaluate("ISREF('" & SHname & "'!A1)")
End Function

Function IsValidSheetName(s As String) As Boolean
    If Len(s) = 0 Or Len(s) > 31 Then Exit Function
    If InStr(s, "\") Then Exit Function
    If InStr(s, "/") Then Exit Function
    If InStr(s, ":") Then Exit Function
    If InStr(s, "|") Then Exit Function
    If InStr(s, "*") Then Exit Function
    If InStr(s, "?") Then Exit Function
    IsValidSheetName = True
End Function

??

Lost

Jerry,

1. Go to the "PT (Master)" tab on the workbook attached to this post.
2. Click the Copy Sheet button.
3. Name that new sheet "a".
4. Now go the "a" sheet.
5. Click the Copy Sheet button.
6. Name that new sheet "b"
7. You should get the error.

HTH!

Lost

Jerry,

You made the copy from the copy, right?

PT (Master) makes "a".

"a" makes "b"

And you didn't get the error?

(This is running on Excel 2003.)


Lost

Jerry,

I tried it again with the file from post #10. When I make a copy of a copy, I get the error.

(If I go to the PT Master and copy to sheet "a", and then go back to PT Master and copy to sheet "b", I don't get the error.

If I go to PT Master and copy to sheet "a", and then go to sheet "a" (copy of a copy) and try to copy to sheet "b", I get the error.)

?

Lost