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
Bookmarks