Error Handling for Duplicate Worksheet Names

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

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






In article <[email protected]>,
MWS <[email protected]> wrote:

> 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

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






In article <[email protected]>,
MWS <[email protected]> wrote:

> 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

"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

--
Regards,
Tom Ogilvy




"MWS" <[email protected]> wrote in message
news:[email protected]...
> 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
>

Thank You JE McGimpsey - It Works Perfectly

"JE McGimpsey" wrote:

> 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
>
>
>
>
>
>
> In article <[email protected]>,
> MWS <[email protected]> wrote:
>
> > 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
>