CODEWITHSUNDEEP
excelvbaerror-handling
brb
brb

Reputation: 1179

Why does error handler not handle a second error?

I am trying to copy a given sheet and rename that copy sheet2, but if sheet2 exists rename it sheet3, and if sheet3 exists rename it sheet4 etc.

I am trying to use an infinite while loop with error handling (i.e. if it tries to rename the sheet and an identical sheet exists, it handles the error and increases the suffix by 1, but if no error occurs, it exits the loop).

Sub Clone()

    Application.ScreenUpdating = False

    Dim ParamsToBeCloned As String
    Dim wsNumber As Long
    Dim suffix As Long

    ParamsToBeCloned = Sheets("Interface").Range("ParamsToBeCloned")

    wsNumber = Sheets(ParamsToBeCloned).index
    Sheets(ParamsToBeCloned).Copy after:=Sheets(wsNumber)

    suffix = 2
    Do While True
        On Error GoTo sheetExists
        ActiveSheet.Name = ParamsToBeCloned & suffix
        Exit Do
sheetExists:
        suffix = suffix + 1
    Loop

    Sheets("Interface").Select

    Application.ScreenUpdating = True

End Sub

This works when Params2 exists to produce Params3, but if both Params2 and Params3 exist, it throws the error

"That name is already taken. Try a different one."

I don't get why it is able to produce Params3 when Params2 exists, but it can't produce Params4 when both Params2 and Params3 exist.

That is, why is the error not being handled the second time?

Upvotes: 0

Views: 197

Answers (2)

JvdV
JvdV

Reputation: 75840

Just an alternative to using On Error:

For x = 2 To 1000
    If Application.Evaluate("ISREF(" & ParamsToBeCloned & x & "!A1)") = False Then
        ActiveSheet.Name = ParamsToBeCloned & x
        Exit For
    End If
Next x

To check if a worksheet exists we can try to see if ISREF returns a TRUE or FALSE. On FALSE, you can name your worksheet.

As per @Peh his comment, you might need a way to check if ParamsToBeCloned & x is a valid worksheet name first.

Upvotes: 2

Pᴇʜ
Pᴇʜ

Reputation: 57683

You need to clear the error to trigger another one I think. Try to add a On Error Goto 0 after suffix = suffix + 1. This might help too VBA Error Handling – A Complete Guide

Also note that this loop will run forever if the active sheet cannot be renamed. This might happen eg if ParamsToBeCloned contains not allowed characters or if it is longer than 32 characters (Excel's limit for sheet names). So you might want to change it into a For loop that has a maximum of eg 1000.

For suffix = 2 To 1000
    On Error Resume Next
    ActiveSheet.Name = ParamsToBeCloned & suffix
    If Err.Number = 0 Then 'no error so successfully renamed
        On Error GoTo 0
        Exit For
    End If
    On Error GoTo 0
    If suffix = 1000 Then MsgBox "Maximum reached could not rename sheet"
Next suffix

Upvotes: 1

Related Questions