CODEWITHSUNDEEP
excelvba
Rachel Chia
Rachel Chia

Reputation: 259

Deleting worksheet error

I am trying to delete all the sheets except sheet 1. I do not know the name of the worksheets except sheet 1. Hence, I used a for loop but it has always been giving an error of Out of Range Subscript on the line Worksheets(s).Delete Thank you for your help in advance. 

Sub clear()
Dim z, y As Integer
Dim s As String
Dim sheet As Worksheet
z = ActiveWorkbook.Sheets.Count
For y = 2 To z Step 1
s = ActiveWorkbook.Sheets(y).Name
Worksheets(s).Delete
Next y
Sheet1.Cells.clear
End Sub

Upvotes: 1

Views: 413

Answers (2)

AlwaysData
AlwaysData

Reputation: 555

I didn't test this but try stepping through the sheets in reverse order. You are probably deleting one of the sheets then trying to reference it in the for loop 

Sub clear()

    Dim z, y As Integer
    z = ActiveWorkbook.Sheets.Count
    For y = z To 2 Step -1
        ActiveWorkbook.Sheets(y).Delete
    Next y
    Sheet1.Cells.clear
End Sub

Upvotes: 1

BruceWayne
BruceWayne

Reputation: 23283

It's a little unclear which sheet you truly want to keep, but how's this? I'm assuming you want to keep, then clear, Sheets("Sheet1"). If not, just edit it to the sheet you do want to keep/clear.

Sub clear()
Dim sheet As Worksheet
For Each sheet In ActiveWorkbook.Sheets
    If sheet.Name <> "Sheet1" Then sheet.Delete
Next sheet
Sheets("Sheet1").Cells.clear
End Sub

If you want to keep Sheet(1) then you would do:

Sub clear()
Dim sheet As Worksheet
For Each sheet In ActiveWorkbook.Sheets
    If sheet.Index <> 1 Then sheet.Delete
Next sheet
Sheets(1).Cells.clear
End Sub

Upvotes: 3

Related Questions