Deleting worksheets

[jacqui]

Can anyone kindly recommend a clever way to delete
worksheets in my workbook based on the following:

I need to delete all worksheets BEFORE a sheet
called "Menu" but keep all the worksheets that follow it.

The number of sheets before "Menu" can be variable.

I'd rather not hard code in any worksheet names.

I'd rather not hard code in the worksheet "Menu" name but
given this is a permanent sheet in the workbook I guess it
makes sense to use this as the identifier. Not sure how
I'd get around it otherwise.

Any suggestions?
Many thanks
Jacqui

 

[Bob Phillips]

Jacqui,

You will have to hard-code the menu name, otherwise there is no known start
point (you could store it in a worksheet cell).

here is the code

Application.DisplayAlerts = False
idx = ActiveWorkbook.Worksheets("menu").Index
For i = idx - 1 To 1 Step -1
Worksheets(i).Delete
Next i
Application.DisplayAlerts = True

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Hi
you may try the following (untested -> make a backup first)

sub foo()
dim i
application.displayalerts = false
for i = 1 to worksheets.count
if lcase(worksheets(i).name) = "menu" then
exit for
end if
worksheets(i).delete
next
application.displayalerts = True
end sub

 

[JE McGimpsey]

One way:

Public Sub DeleteBeforeMenu()
Dim i As Long
Application.DisplayAlerts = False
For i = Worksheets("Menu").Index - 1 To 1 Step -1
Worksheets(1).Delete
Next i
Application.DisplayAlerts = True
End Sub

 

[JE McGimpsey]

that won't work:

Say "Menu" is worksheets(4) of 10

Loop i=1
Delete sheet1, Menu is now sheet3, 9 total sheets left

loop i=2
Delete sheet2, menu is now sheet2, 8 total sheets left

loop i=3
Delete sheet3, menu is still,sheet2 7 total sheets left

loop i=4
Delete sheet4, menu is still,sheet2 6 total sheets left

loop i=5
Delete sheet5, menu is still,sheet2 5 total sheets left

loop i=6
Subscript out of range error.

 

[Frank Kabel]

Hi
you may change the for next loop as below:
sub foo()
dim i
application.displayalerts = false
for i = worksheets.count to 1 step -1
if lcase(worksheets(i).name) = "menu" then
exit for
end if
worksheets(i).delete
next
application.displayalerts = True
end sub

 

[Frank Kabel]

hi JE
just recognized this myself and also my correction is wrong :-(

So for the OP: use JE's method. Have to got some more coffee

 

[Tom Ogilvy]

Actually, JE's method will not work if there are sheets other than
worksheets between the first sheet and the menu sheet.
Worksheets("Menu").Index gives an index number into the sheets collection.
If only worksheets in the workbook, this will work, but if there are chart
sheets and such, then it could give incorrect results.

Public Sub DeleteBeforeMenu()
Dim i As Long
Application.DisplayAlerts = False
On Error Resume Next
For i = Sheets("Menu").Index - 1 To 1 Step -1
Sheets(1).Delete
Next i
On Error GoTo 0
Application.DisplayAlerts = True
End Sub

Would be more accurate and also account for Menu being the first sheet.

 

[JE McGimpsey]

Tom's code is still is problematic since the OP said 'I need to delete
all worksheets BEFORE a sheet called "Menu"', which means that chart
sheets presumably (possibly?) shouldn't be deleted.

perhaps:

Public Sub DeleteBeforeMenu()
Dim i As Long
Application.DisplayAlerts = False
For i = Sheets("Menu").Index - 1 To 1 Step -1
If TypeName(Sheets(i)) = "Worksheet" Then _
Sheets(i).Delete
Next i
Application.DisplayAlerts = True
End Sub

Don't follow the comment about "account for Menu being the first sheet"
- doesn't the For...Next declaration take care of that?

 

[Tom Ogilvy]

Simpler:

Sub DeleteSheets()
Application.DisplayAlerts = False
Do While LCase(Worksheets(1).Name) <> "menu"
Worksheets(1).Delete
Loop
Application.DisplayAlerts = True

End Sub