PDA

View Full Version : Solved: Deleting unwanted worksheets in WB



lynnnow
05-16-2010, 11:16 PM
Hi,

Please help me with this:

I've got a workbook that is created with a default of 3 sheets. I add one more sheet from another workbook, making the total count of sheets to 4. The sheets are named "Day", "Month", "Sheet2" and "Sheet3".

I've written this code to delete the sheets named "Sheet" leaving just the "Day" and "Month" sheets in the WB.

Application.DisplayAlerts = False
For j = 1 To ActiveWorkbook.Sheets.Count

If Left(Sheets(j).Name, 5) = "Sheet" Then
Sheets(j).Delete
End If
Next
Application.DisplayAlerts = True

My query is that the for loop increments to 4 and then there is an out of subscript error. I want the for loop to quit after all sheets name "Sheetx" are deleted.

Please advise:dunno.

Lincoln

lynnnow
05-16-2010, 11:50 PM
Hi,

Took a break and solved my own query. Added a rechecker

Application.DisplayAlerts = False
ReChk: For j = 1 To ActiveWorkbook.Sheets.Count

If Left(Sheets(j).Name, 5) = "Sheet" Then
Sheets(j).Delete
GoTo ReChk
End If
Next
Application.DisplayAlerts = True Thanks for trying.

Blade Hunter
05-16-2010, 11:50 PM
Try this instead :)


Application.DisplayAlerts = False
For Each WS In Worksheets
If Left(WS.Name, 5) = "Sheet" Then WS.Delete
Next
Application.DisplayAlerts = True


Your code will skip sheets even with your recheck.

In your loop it gets to say sheet 2, it then deletes it, then it goes to sheet 3 but sheet 3 is actually the new sheet 2 so it will be skipped because you already tested sheet 2. This is the same as when we delete rows we do it from the bottom up rather than top down.

Using my code will resolve this problem for you.

lynnnow
05-16-2010, 11:56 PM
Thanks Blade Hunter for that. I've used your code instead.