Deleting worksheets by macro

Question

I'm trying to delete all sheets except the first sheet, but I get an

error 9 "subscript out of range" appears.

How can I fix it? Thanks in advance.

Dim Udalenie As Integer 
   If ThisWorkbook.Worksheets.Count > 1 Then
      For Udalenie = 2 To ThisWorkbook.Worksheets.Count
         ThisWorkbook.Sheets(Udalenie).Delete
   Next Udalenie
End If

score 3 · Accepted Answer · answered Apr 23 '14 at 07:00

3

Because each time you delete one sheet, the Excel will remove it from this sheet from Worksheets and the total sheets count will minus 1.

Dim Udalenie As Integer
If ThisWorkbook.Worksheets.Count > 1 Then
For Udalenie = 2 To ThisWorkbook.Worksheets.Count
ThisWorkbook.Sheets(2).Delete
Next Udalenie
End If

answered Apr 23 '14 at 07:00

Taotao

168
7

I need to delete all sheets except first one. How could your answer help, this code deletes only 2 sheet. – Seya Apr 23 '14 at 07:04
@Seya have you tried this code? It doesn't only delete the 2nd sheet. There is a loop set up to run multiple times => sheets.Count ( so if you have 5 sheets the loop will run 4 times ). And each time the loop runs it deletes the 2nd sheet. See explanation of shifting indexes [**HERE**](http://stackoverflow.com/questions/19265508/how-to-select-and-delete-every-3rd-column/19265852#19265852) – Apr 23 '14 at 07:24

Deleting worksheets by macro

1 Answers1