Consulting

Results 1 to 12 of 12

Thread: Rename New Sheet?

  1. #1

    Rename New Sheet?

    Hello,

    its says that it already exists
    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

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    Hello Xld,

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

  4. #4
    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
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  5. #5
    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?

  6. #6
    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.

    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  7. #7
    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
    Attached Images Attached Images

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

  9. #9
    Quote Originally Posted by jolivanes View Post
    Did you check if it is a hidden sheet with that name?
    Hello jolivanes,
    Yes i did and didn't find any sheets.

  10. #10
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Can you post your workbook, see if the same happens for us?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  11. #11
    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.

  12. #12
    Good to see that you resolved the problem.
    Good luck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •