Deleting Sheets Macro

Try

Sub RenameTemplateSheet()

Dim numOfShts As Integer
    numOfShts = 0
If Sheets.Count > 2 Then
    For i = 3 To Worksheets.Count

        If IsEmpty(Worksheets(i).Range("A6")) Then
            numOfShts = numOfShts + 1
        End If
    Next i
   
    Dim sheetsArray() As Variant
    ReDim sheetsArray(numOfShts - 1)
    j = 0
    
    For iSheet = 3 To Worksheets.Count
        If IsEmpty(Worksheets(iSheet).Range("A6")) Then
           sheetsArray(j) = Worksheets(iSheet).Name
           j = j + 1
        End If
    Next iSheet

    Worksheets(sheetsArray).Delete
    
    
Worksheets(1).Range("C9:AI50,AK9:AN50").ClearContents
    
    
Worksheets(2).Name = "TEMPLATE"
Worksheets(2).Range("A1") = "TEMPLATE"

Else
MsgBox "There are only " & Sheets.Count & " sheets in workbook"
End If


End Sub

VBA Noob

Good evening Excel Newbie05

It seems to be because of the line :

For i = 3 To Worksheets.Count

If Worksheets.Count is less than three, then it cannot count backwards unless you qualify the statement with a Step -1 instruction. The loop will do nothing.

As you variables (j in particular) are quantified within the loop, they are set to 0 outside the loop, Excel is not picking up and deleting the sheets it should.

HTH

DominicB

To turn off the sheet delete message add this at the start

Application.DisplayAlerts = False

and this at the end

Application.DisplayAlerts = True

VBA Noob