Deleting Sheets Macro

**Excel Newbie05** · 02-08-2008, 05:36 PM

Hi Everyone,

I wrote the function below so it could
1) Clear all contents in sheet 1 range C9:AI50,AK9:AN50
2) Rename worksheet 2 to "TEMPLATE" and cell A1 in worksheet 2 to "TEMPLATE"
3) Delete all other worksheets in the workbook but sheet 1 and 2.

Now Here is my issue, this code works, expect for when I have only 2 worksheets?? Can someone give me some insight on what I may be doing wrong??

Thanks!

Sub RenameTemplateSheet()

Dim numOfShts As Integer
    numOfShts = 0
    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"


End Sub

**VBA Noob** · 02-08-2008, 05:43 PM

Add a if statement

e,g

If Sheets.Count > 2 Then
'your code
Else
'yourcode

End If

VBA Noob

**Excel Newbie05** · 02-08-2008, 05:57 PM

Hi VBA Noob,

I tried that but doesn't seem to work...

If I add that If Statement then it works properly when their are two sheets, but not when there are more than two...

I am not sure why?

Thanks again!

**VBA Noob** · 02-08-2008, 06:05 PM

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

**dominicb** · 02-08-2008, 06:10 PM

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