Renaming worksheets by VBA

Question

I want to rename workbook sheets with VBA in a loop with if-clauses.

Copied sheets show up with the title "TEMPLATE (i)" for i = 1...38, instead of being renamed with an incrementing index value.

Sub dataExtraction()

Dim user_state As Integer
Dim index As Integer
Dim nums As Integer
Dim i As Integer
Dim x As Integer

Dim status As String
Dim billingMethod As String
 
status = "inaktiv"
billingMethod = "Projektverrechnung"
x = 1

For i = 2 To 500

    If Cells(i, 13).Value = status Then index = index + 1 Else
    If Cells(i, 13).Value = status Then Sheets("TEMPLATE").Copy After:=Sheets(Sheets.Count) Else
    
    Worksheets("TEMPLATE (2)").Activate
    
    If Cells(i, 13).Value = status Then Sheets("TEMPLATE (2)").Name = index Else
    
    Worksheets("dataset").Activate
            
Next i
    
MsgBox (index)
MsgBox "The name of the active sheet is " & ActiveSheet.Name

End Sub

Answer 1

Your main issue is that you are not qualifying the Range (Cells(i, 13)). Qualifying means that you tell VBA from which sheet you want to read. If you omit this, always the active sheet is used.

Now you check for If Cells(i, 13).Value = status multiple time. However, after copying the template sheet, the active sheet changed to the new created sheet and therefore the last If-statement fails (because now the Cells(i, 13) is read from that sheet).

Some more remarks to your code:

Your usage of the If-statement is sub-optimal. Don't repeat the same check multiple times, but instead use the syntax shown below, using End If.

As said, after copying a sheet, the new sheet is already active, no need to activate it again. This will especially fail when a sheet "TEMPLATE (2)" already exists because the new created sheet will be for example "TEMPLATE (3)" and you rename the wrong sheet

One minor thing: Make it a habit to use Long instead of Integer, especially when dealing with row numbers. This avoids numeric overflow errors.

Your code could look like this. Note that I am using a With-statement and qualify .Cells(i, 13) by putting a dot at the beginning. This ensures that the cell is always red from the dataset sheet, no matter which sheet is currently active.

With ThisWorkbook.Worksheets("dataset")
    For i = 2 To 500
        If .Cells(i, 13).Value = Status Then
            Index = Index + 1
            ThisWorkbook.Sheets("TEMPLATE").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        
            ActiveSheet.Name = Index
        End If
    Next i
End With