Adding Sheets With Sequential Names

Question

I need to write a macro that adds a new sheet when executed. The sheet name will be "Combined-n" where n is an integer. I want it to try add a new sheet named "Combined-1". However, if the sheet "Combined-1" already exists (since this macro can be executed multiple times), I want it to add a sheet called "Combined-2" and so on. I tried a few different things including the code below, but when I execute it nothing happens.

Dim i As Integer
Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets
WS.Activate
For i = 1 To Worksheets.Count
If WS.Name = "Combined-" & i Then
Sheets.Add(Before:=Sheets("Sheet1")).Name = "Combined-" & i + 1
End If
Next i
Next WS

I also tried:

Dim i As Integer

For i = 1 To Worksheets.Count
   If Worksheets(i).Name = "Combined-" & i Then
   Sheets.Add(Before:=Sheets("Sheet1")).Name = "Combined-" & i + 1
End If
Next i

Answer 1

Write a function whose only job is to return the name of the next "Combined-N" sheet. I'd do this by counting the number of sheets that have a name that starts with "Combined-", and adding 1 to that number, and then incrementing until "Combined-" concatenated with that number is a sheet name that doesn't already exist.

So, I'd have a GetNextCombinedSheetName function to do this, and a SheetNameExists function to determine whether a given sheet name exists in an optionally-specified Workbook's Worksheets collection.

Something like this:

Public Function GetNextCombinedSheetName() As String
    Const namePrefix As String = "Combined-"

    Dim currentcount As Long

    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If Left(ws.Name, Len(namePrefix)) = namePrefix Then
            currentCount = currentCount + 1
        End If
    Next

    Dim nextName As String
    Do 'ensure the name doesn't already exist - increment if it does:
        nextName = namePrefix & currentCount + 1
    Loop While SheetNameExists(nextName)

    GetNextCombinedSheetName = nextName
End Function

Private Function SheetNameExists(ByVal sheetName As String, Optional ByVal wb As Workbook = Nothing) As Boolean
    If wb Is Nothing Then Set wb = ThisWorkbook
    Dim ws As Worksheet
    On Error Resume Next ' swallow index out of bounds error 9
    Set ws = wb.Worksheets(sheetName)
    On Error GoTo 0
    SheetNameExists = Not ws Is Nothing
End Function

With that, you can add a new sheet and just name it:

Dim newSheet As Worksheet
Set newSheet = ThisWorkbook.Worksheets.Add
newSheet.Name = GetNextCombinedSheetName

Note how every Worksheets member call (or Sheets - but why are you using the two interchangeably and inconsistently?) is properly qualified with a Workbook object: your code appears to have several implicit ActiveWorkbook references, and this only works because the ActiveWorkbook happens to be the host ThisWorkbook document - it may not always be the case (especially as you learn to stop Activate-ing and Select-ing things), and you don't want your code to assume it is: life is much simpler when we systematically qualify workbook and worksheet member calls.