Error Handling for Duplicate Worksheet Names

[Guest]

I have the following code that allows the user the option of creating a copy
of the active worksheet and then names the copied worksheet:

Private Sub cmdCopy_Click()
Dim sh As Worksheet
With ThisWorkbook ' or ActiveWorkbook
Set sh = .Worksheets.Add(After:= _
.Worksheets(.Worksheets.Count))
.Worksheets("Populate Scorecard....").Cells.Copy _
Destination:=sh.Cells
End With

sh.Name = Range("b2").Value

Issue: I would like to create some sort of error handling that if the user
is trying to copy a worksheet that was previously copied/named, abort the
copying process and return the user to another worksheet via GoTo.

Can anyone help?

Any and All Assistance Will Be Appreciated - Thanks In Advance

 

[JE McGimpsey]

one way:

This routine doesn't do anything if there's already a sheet with the
name that's in 'Populate Scorecard...'!B2, and doesn't "go" anywhere in
that case, unless you populate the commented line:

Private Sub cmdCopy_Click()
Dim sh As Worksheet
Dim shCopy As Worksheet
With ThisWorkbook.Worksheets
Set shCopy = .Item("Populate Scorecard....")
On Error Resume Next
Set sh = .Item(shCopy.Range("B2").Text)
On Error GoTo 0
If sh Is Nothing Then
Set sh = .Add(After:=.Item(.Count))
shCopy.Cells.Copy Destination:=sh.Cells
sh.Name = sh.Range("B2").Text
Else
'Application.GoTo ...
End If
End With
End Sub

 

[Tom Ogilvy]

"Another sheet" is not well defined. Nonetheless:

Private Sub cmdCopy_Click()
Dim sh As Worksheet, Dim sh1 as Worksheet
With ThisWorkbook ' or ActiveWorkbook
On Error Resume Next
set sh1 = Worksheets(Range("B2").Value)
On Error goto 0
if sh1 is nothing then
Set sh = .Worksheets.Add(After:= _
.Worksheets(.Worksheets.Count))
.Worksheets("Populate Scorecard....").Cells.Copy _
Destination:=sh.Cells
sh.Name = Range("b2").Value

else
Application.GoTo .Worksheets(1).Range("A1")
' or
' Application.Goto sh1.Range("A1")
End if
End With

 

[Guest]

Thank You JE McGimpsey - It Works Perfectly

one way:

This routine doesn't do anything if there's already a sheet with the
name that's in 'Populate Scorecard...'!B2, and doesn't "go" anywhere in
that case, unless you populate the commented line:

Private Sub cmdCopy_Click()
Dim sh As Worksheet
Dim shCopy As Worksheet
With ThisWorkbook.Worksheets
Set shCopy = .Item("Populate Scorecard....")
On Error Resume Next
Set sh = .Item(shCopy.Range("B2").Text)
On Error GoTo 0
If sh Is Nothing Then
Set sh = .Add(After:=.Item(.Count))
shCopy.Cells.Copy Destination:=sh.Cells
sh.Name = sh.Range("B2").Text
Else
'Application.GoTo ...
End If
End With
End Sub