Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling for Duplicate Worksheet Names
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 , MWS 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling for Duplicate Worksheet Names
"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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling for Duplicate Worksheet Names
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 , MWS 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate Range Names by worksheet | Excel Discussion (Misc queries) | |||
Finding/removing duplicate names in a worksheet | Excel Worksheet Functions | |||
Duplicate names on copied worksheet | Links and Linking in Excel | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
Error handling with a handling routine | Excel Programming |