Rename New Sheet?

Hello,

its says that it already exists :wot
but its not, what do we wrong?

i copy my original sheet to a new sheet that's works and create with the name 'sheetlist (2)'
but when we wanna rename it with a new name it not allows me

Code:

---


Function CopyRenameSheet(NameSheet As String) As Boolean
Set _start = Nothing


CopyRenameSheet= False


On Error Resume Next
Set _start = ActiveWorkbook.WorkSheets("sheetlist (2)")
On Error GoTo 0




If _start Is Nothing Then


CopyRenameSheet= True


ActiveWorkbook.WorkSheets("sheetlist").Visible = True
ActiveWorkbook.WorkSheets("sheetlist").Copy After:=ActiveWorkbook.Sheets("HistoryItems")
         
Set OldName = _start
On Error Resume Next


For Each mysheets In ThisWorkbook.Sheets
    If mysheets .Name = OldName  Then
        OldName.Name = NameSheet
    End If
Next mysheets


OldName.Name = NameSheet


On Error GoTo 0


End If


End Function

---

Thanks

Don't over-complicate it

Code:

---

Function CopyRenameSheet(NameSheet As String) As Boolean

    With ActiveWorkbook
   
        .Worksheets("sheetlist").Visible = True
        .Worksheets("sheetlist").Copy After:=ActiveWorkbook.Sheets("HistoryItems")
        .ActiveSheet.Name = NameSheet
    End With
End Function

---

Hello Xld,

i still gets the 1004 error
that the name of the sheet already taken :(

You need some more error handling :) Note that '_start' is an illegal variable name.

Code:

---

Option Explicit

Function CopyRenameSheet(strSheetName As String) As BooleanDim xlSheet As Worksheet


    On Error GoTo err_Handler
    Set xlSheet = ActiveWorkbook.Worksheets("sheetlist")
    xlSheet.Visible = xlSheetVisible
    xlSheet.Copy After:=ActiveWorkbook.Sheets("HistoryItems")
    ActiveSheet.Name = strSheetName
    CopyRenameSheet = True


lbl_Exit:
    Set xlSheet = Nothing
    Exit Function
err_Handler:
    Beep
    MsgBox "The worksheet '" & strSheetName & "' has already been created"
    Err.Clear
    Application.DisplayAlerts = False
    ActiveWorkbook.Worksheets("sheetlist (2)").Delete
    Application.DisplayAlerts = True
    CopyRenameSheet = False
    GoTo lbl_Exit
End Function

---

Hi gMayor,

thanks for the help also

but still it makes the sheetlist (2) and then i got the error message "The worksheet '" & strSheetName & "' has already been created"

but i was looking at the sheet when it added it but no name with the strsheetname :( so i don't get it why it says it already been created if not


edit:

weird if i change the name it makes the sheet
but still don't understand it why my other name exists if not?

The code copies the sheet "sheetlist" and pastes it after the sheet "HistoryItems" as "sheetlist (2)". The code then attempts to name "sheetlist (2)" with whatever name you have supplied with strSheetname. If a sheet with that name already exists you get the message.

Hi gMayor,

yes i know, but we don't have that sheet and still it say that message

edit:

okay thanks for the big help guys
i just use some other names so it will do

thank alot :thumb

Did you check if it is a hidden sheet with that name?

Quote:

---

Originally Posted by jolivanes

Did you check if it is a hidden sheet with that name?

---

Hello jolivanes,
Yes i did and didn't find any sheets.

Can you post your workbook, see if the same happens for us?

hello xld,

it seems working now
and yes it was true it was hidden very-well
i detect it after i checked out the whole project in developer

sorry for the misunderstanding
and thank you all for the awesome support. :thumb

Good to see that you resolved the problem.
Good luck