Worksheet naming

[shiro]

I had create a macro that create a copy of the
active sheets on another workbook.It works OK.
But I having problem with renaming the copied
sheet.
I have below simple code for renaming the copied
sheet :

With activesheet.
.Name = .Range ("A12").Value
.Save
End With

But if the sheet with that name already exist,it will
produce an error.How to trap that.I want the old
sheet whit that name is replaced with the new one.

Thank's in advance

Rgds,

Shiro

 

[Mike H.]

You could do this a couple ways. One way would be to test for the existance
before you do the renaming. To do that:

Sub test()
Dim NameOfSheet As String
Dim NameToBe As String


On Error GoTo FoundItAlready
Let NameToBe = "Frogs"
NameOfSheet = ActiveSheet.Name
ActiveSheet.Name = NameToBe


GoTo SkipOver 'if you get to here, it works so don't execute the code below.

FoundItAlready:
Sheets(NameToBe).Select
ActiveSheet.Name = "SomethingElse"
Sheets(NameOfSheet).Select
ActiveSheet.Name = NameToBe


SkipOver:


End Sub

What the above code does is name the current sheet to "Frogs" but if the
sheet "Frogs" already exists, it names it "SomethingElse" and then names the
sheet you want to be "Frogs" to "Frogs". This is a one-time macro with no
loops and you'd have to build looping in and also create variables for the
name to change the old one to so that you don't then try to creat multiple
sheets with the name "SomethingElse" but I think you'll get the idea with
this. HTH. Let me know if you need additional help.

 

[Kataklist]

If he wants to replace it i think the easiest way is simply to delete the old
one if it exists, so:
Dim OldName As String
Dim NewName As String
OldName = "This"
NewName = "Whatever"

If ActiveSheet.Name = NewName Then
ActiveSheet.Delete
Sheets(Oldname).Select
ActiveSheet.Name = NewName
Else
ActiveSheet.Name = NewName

Hope I didnt get anything wrong here but i think my idea is shorter anyways...