PDA

View Full Version : Rename New Sheet?



GhostofDoom
12-13-2019, 11:03 AM
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






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

Bob Phillips
12-13-2019, 01:02 PM
Don't over-complicate it



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

GhostofDoom
12-13-2019, 02:32 PM
Hello Xld,

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

gmayor
12-13-2019, 10:23 PM
You need some more error handling :) Note that '_start' is an illegal variable name.


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

GhostofDoom
12-14-2019, 02:19 AM
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?

gmayor
12-14-2019, 06:30 AM
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.

GhostofDoom
12-14-2019, 06:38 AM
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

jolivanes
12-15-2019, 01:31 PM
Did you check if it is a hidden sheet with that name?

GhostofDoom
12-15-2019, 07:10 PM
Did you check if it is a hidden sheet with that name?

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

Bob Phillips
12-16-2019, 06:00 AM
Can you post your workbook, see if the same happens for us?

GhostofDoom
12-16-2019, 10:00 AM
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

jolivanes
12-16-2019, 10:34 PM
Good to see that you resolved the problem.
Good luck