Delete Worksheets in Excel Workbook with a Condition?

[Bertha94]

I have a workbook with many sheets, and I am trying to delete the sheets that have a certain value in cell A1. My code will not continue going to the next sheets once the condition is not met. Can anybody help with this code?

Dim I As Integer

For I = 1 To Sheets.Count
ActiveSheet.Ran ge("A1").Selec t
If ActiveCell = "BUDGET" Then
Application.Dis playAlerts = False
ActiveSheet.Del ete
Application.Dis playAlerts = True

End If
Next

[kadghar]

I have a workbook with many sheets, and I am trying to delete the sheets that have a certain value in cell A1. My code will not continue going to the next sheets once the condition is not met. Can anybody help with this code?

Dim I As Integer

For I = 1 To Sheets.Count
ActiveSheet.Ran ge("A1").Selec t
If ActiveCell = "BUDGET" Then
Application.Dis playAlerts = False
ActiveSheet.Del ete
Application.Dis playAlerts = True

End If
Next

this is because once you delete Worksheets(1) the one that used to be Worksheets(2) turns into worksheets(1)

i recomend you to use a DO that only increases the counter when you dont delete the sheet, something like

[CODE=vb]i =1
application.dis playalerts = false
do
if worksheets(i).c ells(1,1).value = "BUDGET" then
worsheets(i).de lete
else
i = i +1
if i > worksheets.coun t then exit do
end if
loop
application.dis playalerts = true[/CODE]

hope that helps

[Tig201]

I have a workbook with many sheets, and I am trying to delete the sheets that have a certain value in cell A1. My code will not continue going to the next sheets once the condition is not met. Can anybody help with this code?

Dim I As Integer

For I = 1 To Sheets.Count
ActiveSheet.Ran ge("A1").Selec t
If ActiveCell = "BUDGET" Then
Application.Dis playAlerts = False
ActiveSheet.Del ete
Application.Dis playAlerts = True

End If
Next

You could also reverse your Loop to work from the back of the workbook to the front.

[kadghar]

You could also reverse your Loop to work from the back of the workbook to the front.

yeap, that's an easier way :)

[SammyB]

Even easier is a for each loop. Also, you want to use the Worksheets collection instead of the Sheets collection. A Sheet object can be a Chart sheet and looking at A1 will crash and burn!

Option Explicit
Sub Macro1()
	Dim ws As Worksheet
	Application.DisplayAlerts = False
	For Each ws In Worksheets
		If ws.Cells(1, 1) = "Budget" Then _
			ws.Delete
	Next ws
	Application.DisplayAlerts = True
End Sub

[Bertha94]

Great. Thank you very much for the help.