worksheet with same name error

J

John D. Inkster

I have a macro that I use to make a copy of the active worksheet . It
works great, if I run the macro again with the same value in "f4", I
get a vb error. Is there a way to check the name in "f4" against all
worksheet names (there could be 50 sheets) and trap the error?

Dim my_sheet As String
my_sheet = Range("f4").Value
ActiveSheet.Copy After:=Sheets(1)
ActiveSheet.Name = my_sheet

I will then use:


MsgBox "Sheet Saved", vbOKOnly + vbExclamation
Else
MsgBox "Duplicate name Sheet Not Saved", _
vbOKOnly + vbExclamation

End If
End Sub

Thanks for any help!

John
 
D

Dave Peterson

How about just checking to see if the rename was successful?

Dim my_sheet As String
my_sheet = Range("f4").Value
ActiveSheet.Copy After:=Sheets(1)

on error resume next
ActiveSheet.Name = my_sheet
if err.number <> 0 then
msgbox "rename failed--either invalid name " _
& "or a worksheet with that name already exists"
err.clear
end if
 
J

John D. Inkster

That does check for duplicates but it dosen't stop the sheet from
being copied, should I move "ActiveSheet.Copy After:=Sheets(1)" and if
so where should I put it?

John
 
M

Martin Fishlock

Hi,

I think that Dave's suggstion was spot on.

I would personally check prior to copying the sheet which involves extra
work but in my opinion does not leave a hole in the process with a worksheet
that has an unkown name.

Try this.

Dim my_sheet As String
Dim ws as worksheet

err.clear
on error resume next

set ws = activesheet
my_sheet = Range("f4").Value
worksheets(my_sheet).activate
if err.number = 0 then
msgbox "Already have a sheet with the name " _
& my_sheet, vbokonly,"Rename error"
else
err.clear
ws.Copy After:=Sheets(1)
ActiveSheet.Name = my_sheet
if err.number <> 0 then
msgbox "rename failed--either invalid name " _
& "or a worksheet with that name already exists"
err.clear
end if
end if
on error goto 0
 
D

Dave Peterson

If you don't want the copied sheet to exist if there was an error renaming it,
you could just delete it in that error checking portion.

Dim my_sheet As String
my_sheet = Range("f4").Value
ActiveSheet.Copy After:=Sheets(1)

on error resume next
ActiveSheet.Name = my_sheet
if err.number <> 0 then
application.displayalerts = false
activesheet.delete
application.displayalerts = true
msgbox "rename failed--either invalid name " _
& "or a worksheet with that name already exists"
err.clear
end if
 
B

Bob Phillips

Dim my_sheet As String
Dim sh As Worksheet
my_sheet = Range("f4").Value
On Error Resume Next
Set sh = Worksheets(my_sheet)
On Error GoTo 0
If sh Is Nothing Then
ActiveSheet.Copy After:=Sheets(1)
ActiveSheet.Name = my_sheet
Else
MsgBox "Sheet " & my_sheet & " already exists"
End If


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
D

Dave Peterson

Maybe the same idea without activating the sheet--might be useful if that
worksheet exists but is hidden.

Dim my_sheet As String
Dim ws as worksheet
dim testwks as worksheet

set ws = activesheet

my_sheet = Range("f4").Value

set testwks = nothing
on error resume next
set testwks = worksheets(my_sheet)
on error goto 0

if testwks is nothing then
'keep going
else
msgbox "Already have a sheet with the name " _
& my_sheet, vbokonly,"Rename error"
exit sub
end if

ws.Copy _
After:=Sheets(1)

on error resume next
ActiveSheet.Name = my_sheet
if err.number <> 0 then
msgbox "rename failed--invalid name"
application.displayalerts = false
activesheet.delete
application.displayalerts = true
err.clear
end if
end if
on error goto 0

(Untested--watch for typos.)
 
J

John D. Inkster

Got it thanks
John

If you don't want the copied sheet to exist if there was an error renaming it,
you could just delete it in that error checking portion.

Dim my_sheet As String
my_sheet = Range("f4").Value
ActiveSheet.Copy After:=Sheets(1)

on error resume next
ActiveSheet.Name = my_sheet
if err.number <> 0 then
application.displayalerts = false
activesheet.delete
application.displayalerts = true
msgbox "rename failed--either invalid name " _
& "or a worksheet with that name already exists"
err.clear
end if
Click to expand...
 
J

John D. Inkster

Thanks Guys, I'm glad you had nothing better to do on a Sunday
Either.............. <G>

John Inkster
 
Top