Check if Sheet Exists - if it does, add a 1,2,3, etc to the end of the sheet name

[larinda4]

Hi Excel folks,

I've been trying to modify my code to check if the sheet name already exists. If it does exist, I want it to add a -1, -2, etc if the sheetname already exists. There could be up to 5, so if the sheetname already exists, I want it to add the next number available.

  Dim wsOriginal As Worksheet
    Dim wsNew As Worksheet
    Dim wsOrderInput As Worksheet
    Dim sheetName As String


    ' Set references to the relevant worksheets
    Set wsOriginal = ThisWorkbook.Sheets("MR#") ' Original "MR#" sheet
    Set wsOrderInput = ThisWorkbook.Sheets("ORDER INPUT") ' ORDER INPUT sheet
    
    ' Create a copy of the "MR#" sheet and move it to the far right
    wsOriginal.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    Set wsNew = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) ' New copied sheet
    
   
    'Rename the new sheet
    sheetName = "MR#" & ThisWorkbook.ActiveSheet.Range("N3").Value
    ThisWorkbook.ActiveSheet.Name = sheetName

any help would be greatly appreciated.

 

[MARK858]

Maybe something like

Sub larinda()
    Dim wsOriginal As Worksheet
    Dim wsNew As Worksheet
    Dim wsOrderInput As Worksheet
    Dim sheetName As String
    Dim i As Long


    ' Set references to the relevant worksheets
    Set wsOriginal = ThisWorkbook.Sheets("MR#")  ' Original "MR#" sheet
    Set wsOrderInput = ThisWorkbook.Sheets("ORDER INPUT") ' ORDER INPUT sheet
 
    ' Create a copy of the "MR#" sheet and move it to the far right
    wsOriginal.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    Set wsNew = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) ' New copied sheet
 
 
    'Rename the new sheet
    sheetName = "MR#" & ThisWorkbook.ActiveSheet.Range("N3").Value
 
    If Not Evaluate("isref('" & sheetName & "'!A1)") Then
        ThisWorkbook.ActiveSheet.Name = sheetName
        Exit Sub
    Else
        For i = 1 To 6
            sheetName = "MR#" & ThisWorkbook.ActiveSheet.Range("N3").Value & "-" & i
            If Not Evaluate("isref('" & sheetName & "'!A1)") Then
                ThisWorkbook.ActiveSheet.Name = sheetName
                Exit Sub
            End If
        Next
    End If

End Sub

Please note I think you'll need to redefine the

sheetName = "MR#" & ThisWorkbook.ActiveSheet.Range("N3").Value

line but not sure exactly what sheet you want the name from at that time (I suspect wsOrderInput), at the moment the new sheet will be the activesheet at that time

 

[Skyybot]

When adding the new Sheet use this Function. I think it will deliver the required results. Remember to test on a copy of your Workbook.

Example:

Set NewSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
With NewSheet
      .Name = CheckSheetName(SomeCellValueHere)
End With

The Function:

Function CheckSheetName(ByVal sheet_name As String) As String
Dim sht As Worksheet
Dim i As Integer
For Each sht In ThisWorkbook.Sheets
    If sheet_name = Left(sht.Name, Len(sheet_name)) Then
        i = i + 1
    End If
Next sht
CheckSheetName = sheet_name & "-" & i
End Function