Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Dec 2016
    Posts
    205
    Thanks
    112
    Thanked 6 Times in 6 Posts

    Runtime error 1004: Cannot rename a sheet to the same name

    Hello,

    the code creates a sheet and copy to it sheet "project" content. Most of the time it works fine, but sometimes the code fails and i get the error message as shown in the picture. I figured by using WSCount = Worksheets.Count, the new renamed sheet will never have the same name of an existing sheet.

    Code:
    Sub copysheet()
    Dim WSCount As Long
    Dim NamedRange As Variant
    WSCount = Worksheets.Count
    Dim MySheetName As String
    Dim ws As Worksheet
    Set ws = Worksheets("Project")
    
    Dim clipboard As MSForms.DataObject
    Set clipboard = New MSForms.DataObject
    
    Application.DisplayAlerts = False
    Application.CutCopyMode = False
    Application.ScreenUpdating = False
    
        ws.Copy After:=Sheets(Sheets.Count)
    
        ActiveSheet.Name = "Proj " & WSCount + 10
    
    For Each NamedRange In ActiveSheet.Names
        NamedRange.Delete
    Next NamedRange
    
    clipboard.clear
    
    Application.ScreenUpdating = True
    
    End Sub
    Is there anything i can do to make this code work all the time and avoid this error?

    Thanks in advance for your help.


    Runtime 1004 .png

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    3,094
    Thanks
    171
    Thanked 817 Times in 746 Posts
    Abouya,

    No need for a reference and clipboard. Removed unneeded code lines.

    Code:
    Sub copysheet()
    On Error GoTo errorhandler
    '-----------------------------
    'DECLARE AND SET VARIABLES
        Dim WSCount As Long, NamedRange
        Dim ws As Worksheet
        Set ws = Worksheets("Project")
        WSCount = Worksheets.Count
    '-----------------------------
    'CODE PREP
        Application.DisplayAlerts = False
        Application.ScreenUpdating = False
    '-----------------------------
    'COPY TO NEW SHEET AND RENAME
        ws.Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = "Proj " & WSCount + 10
    '-----------------------------
    'REMOVE NAMED RANGES
        For Each NamedRange In ActiveSheet.Names
            NamedRange.Delete
        Next NamedRange
    '-----------------------------
    'CLEANUP
        Set ws = Nothing
    '-----------------------------
    'RESET
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
    '-----------------------------
    'ERROR HANDLING IF SHEET(S) ARE DELETED BETWEEN CODE RUNS
    errorhandler:
        WSCount = WSCount + 1
        Resume
    End Sub
    Note: You will generate the error if you delete a sheet then rerun the code. The worksheet count drops by one which is a number used by the previous sheet created. If such is the case, I added error handling to increase the sheet number by one then rename the sheet. It will handle the error as many times as needed (for each deleted sheet).

    HTH,
    Maud
    Last edited by Maudibe; 2017-10-11 at 22:55. Reason: added error handling

  3. #3
    3 Star Lounger
    Join Date
    Dec 2016
    Posts
    205
    Thanks
    112
    Thanked 6 Times in 6 Posts
    This is brilliant Maud. Great explanation and detailed code. Thank you so much sir. I really appreciate.


  4. The Following 2 Users Say Thank You to abouya For This Useful Post:

    Maudibe (2017-10-11)

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    3,094
    Thanks
    171
    Thanked 817 Times in 746 Posts
    Anytime!

  6. The Following User Says Thank You to Maudibe For This Useful Post:

    abouya (2017-10-12)

  7. #5
    3 Star Lounger
    Join Date
    Dec 2016
    Posts
    205
    Thanks
    112
    Thanked 6 Times in 6 Posts
    Hello Maud,

    I tried the code but it keeps looping and excel becomes unresponsive. I waited about 5 minutes but it still unresponsive. I have absolutely no idea why this is happening especially that your code makes more sense.

    Best Regards.

  8. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    3,094
    Thanks
    171
    Thanked 817 Times in 746 Posts
    There is something causing an error other than the deletion of sheets that resumes the code. I believe it is related to the named ranges. I will need to add additional error checking for that and limitations to the number of iterations for error handling of deleted sheets. Will post back later this evening.

    Maud

  9. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    3,094
    Thanks
    171
    Thanked 817 Times in 746 Posts
    Abouya,

    Dummy me, I neglected to copy over the most important line....Exit Sub (highlighted in blue). Instead of exiting, the code was flowing right into the error handling a creating a continuous loop. While I was at it, I limited the number of iterations for the error handling code to 5 cycles in case an error exists in the deletion of the namedRanges. This will prevent a continuous loop as well.

    Code:
    Sub copysheet()
    On Error GoTo errorhandler
    '-----------------------------
    'DECLARE AND SET VARIABLES
        Dim WSCount As Long, errcount As Integer, NamedRange
        Dim ws As Worksheet
        Set ws = Worksheets("Project")
        WSCount = Worksheets.Count
    '-----------------------------
    'CODE PREP
        Application.DisplayAlerts = False
        Application.ScreenUpdating = False
    '-----------------------------
    'COPY TO NEW SHEET AND RENAME
        ws.Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = "Proj " & WSCount + 10
    '-----------------------------
    'REMOVE NAMED RANGES
        For Each NamedRange In ActiveSheet.Names
            NamedRange.Delete
        Next NamedRange
    '-----------------------------
    'CLEANUP
        Set ws = Nothing
    '-----------------------------
    'RESET
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
        Exit Sub
    '-----------------------------
    'ERROR HANDLING IF SHEET(S) ARE DELETED BETWEEN CODE RUNS
    errorhandler:
        If errcount <= 5 Then
            WSCount = WSCount + 1
            errcount = errcount + 1
            Resume
        End If
    End Sub

  10. The Following User Says Thank You to Maudibe For This Useful Post:

    abouya (2017-10-12)

  11. #8
    3 Star Lounger
    Join Date
    Dec 2016
    Posts
    205
    Thanks
    112
    Thanked 6 Times in 6 Posts
    Now i understand why. Great coding skills. Thank you so much. Have a wonderful evening sir.

Tags for this Thread

Posting Permissions

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