CODEWITHSUNDEEP
excelvba
tester12341234
tester12341234

Reputation: 9

How to have VBA check if there is a current worksheet with a given ws.name and adjusting name of new worksheet if so?

Currently working on a macro that takes range("A2") value and makes it the worksheet name. That functionality is working, the issue is when the value for A2 is the same in two worksheets and then an error occurs because worksheets cannot have same name. Is there a way to have vba check for the same name in the workbook sheets before trying to name a worksheet, and adjust the new worksheet name by adding a "1" to the end or something?

Sub setSheetNameB2()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
  'ws.Name = ws.Range("A2") commented out for test
    '-------------------------
    ws.Name = RemoveSpecialCharactersAndTruncate(ws.Range("A2"))

    
    
    
    '------------------------------
Next

End Sub

Upvotes: 0

Views: 38

Answers (2)

Tim Williams
Tim Williams

Reputation: 166511

Allowing for >2 sheets with same name...

Sub setSheetNameB2()
    Dim wb As Workbook
    Dim ws As Worksheet, indx As Long, nm As String, length As Long
    
    Set wb = ActiveWorkbook
    For Each ws In wb.Worksheets
        nm = RemoveSpecialCharactersAndTruncate(ws.Range("A2").Value)
        length = Len(nm)
        If length > 0 Then
            indx = 2
            Do While SheetExists(nm, wb)
                nm = Left(nm, length) & "(" & indx & ")"
                indx = indx + 1
            Loop
            ws.name = nm
        End If
    Next

End Sub

Function SheetExists(SheetName As String, Optional wb As Excel.Workbook)
    Dim s As Excel.Worksheet
    If wb Is Nothing Then Set wb = ThisWorkbook
    On Error Resume Next
    Set s = wb.Sheets(SheetName)
    On Error GoTo 0
    SheetExists = Not s Is Nothing
End Function

Function RemoveSpecialCharactersAndTruncate(v)
    'skipped the "remove special chars"
    RemoveSpecialCharactersAndTruncate = Left(v, 27) 'allow room for suffix!
End Function

Upvotes: 0

taller
taller

Reputation: 18898

Please try.

Sub setSheetNameB2()
    Dim ws As Worksheet, sShtName As String
    sShtName = Range("A2").Value ' RemoveSpecialCharactersAndTruncate if needed
    On Error Resume Next
    Set ws = Sheets(sShtName)
    ' sheet is exist
    If Err.Number = 0 Then sShtName = sShtName & "1"
    On Error GoTo 0
    Sheets.Add.Name = sShtName
End Sub

Below code obtains the same result w/o using error tracking.

Sub setSheetNameB2_2()
    Dim ws As Worksheet, sShtName As String
    sShtName = Range("A2").Value ' RemoveSpecialCharactersAndTruncate if needed
    For Each ws In ThisWorkbook.Sheets
        If VBA.StrComp(ws.Name, sShtName) = 0 Then
            sShtName = sShtName & "1"
        End If
    Next
    Sheets.Add.Name = sShtName
    Set ws = ActiveSheet ' get the sheet object named B2
End Sub

Upvotes: 0

Related Questions