CODEWITHSUNDEEP
excelvbaworksheet
chrisphils26
chrisphils26

Reputation: 39

Adding Sheets With Sequential Names

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

Upvotes: 1

Views: 1531

Answers (2)

Nandu Tayade
Nandu Tayade

Reputation: 11

@chrisphils26 - you can try below code also 

Option Explicit

Sub GetAvailableSheeName()

Dim sht As Worksheet
Dim temp_sht
Dim sht_name, last_sht As String
Dim shtNumber
Dim temp_counter, loop_i, counter, num As Integer

Const Available_sht As String = "Combined-"

temp_counter = 0
For Each sht In ThisWorkbook.Worksheets

    If LCase(Left(sht.name, Len(Available_sht))) = LCase(Available_sht) Then

        shtNumber = Split(sht.name, "-")(1)

        If IsNumeric(shtNumber) Then
            If shtNumber > temp_counter Then
                temp_counter = shtNumber
                last_sht = sht.name
            End If

        Else
            sht_name = sht.name

        End If

    Else
            sht_name = sht.name
    End If

Next sht

If temp_counter = 0 Then

   ThisWorkbook.Sheets.Add(After:=Sheets(sht_name)).name = "Combined-1"
Else

   ThisWorkbook.Sheets.Add(After:=Sheets(last_sht)).name = "Combined-" & temp_counter + 1

    For loop_i = 1 To temp_counter + 1

        For Each sht In ThisWorkbook.Worksheets
             counter = 0
             If LCase("Combined-") & loop_i = LCase(sht.name) Then

               counter = 1
               Exit For
             End If

        Next sht

         If counter = 0 Then
            If loop_i = 1 Then
              ThisWorkbook.Sheets.Add(Before:=Sheets(1)).name = "Combined-" & loop_i
            Else
              num = loop_i - 1
              ThisWorkbook.Sheets.Add(After:=Sheets("Combined-" & num)).name = "Combined-" & loop_i
            End If

         End If

        Next loop_i
End If

End Sub

Upvotes: 1

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

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.

Upvotes: 2

Related Questions