Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet naming
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet naming
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet naming
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... "Mike H." wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet naming | Excel Discussion (Misc queries) | |||
Naming A Worksheet | Excel Programming | |||
Naming worksheet | Excel Programming | |||
Naming Worksheet | New Users to Excel | |||
Naming a worksheet help? | Excel Worksheet Functions |