Delete range of Worksheets

[WLMPilot]

I am working on a scheduling system using macros in Excel. I use 26
commandbuttons to goto their respective payperiod (ie worksheet). Since I am
in a testing stage, I created a macro (executed by clicking a commandbutton),
that will reset everything so I can start from scratch again. During this
reset, I need the macro to delete the 26 worksheets, which were all named
differently, based on the payperiod it represents.

How do I delete those 26 worksheets?

Example of worksheet names:
Jan 1 - Jan 13
Jan 15 - Jan 28
Jan 29 - Feb 11

Thanks for your help!

Les

 

[Barb Reinhardt]

Well, you could do something like this. I'm assuming you are running this
from the activesheet. You also will need to delete the buttons on that
sheet, I suspect.

Sub Test()


Dim WS As Worksheet
Dim WB As Workbook
Set WB = ThisWorkbook
For Each WS In WB.Worksheets
If WS.Name <> ActiveSheet.Name Then
Application.DisplayAlerts = False
WS.Delete
Application.DisplayAlerts = True
End If
Next WS

Dim myButton As Button
For Each myButton In ActiveSheet.Buttons
Debug.Print myButton.Caption

Next myButton

End Sub

 

[Wigi]

Hi

For instance with a small loop.


Sub longcolumn()

Dim i As Integer

Application.DisplayAlerts = False

For i = 1 To 26

Worksheets(i).Delete

Next

Application.DisplayAlerts = True

End Sub



This will delete the FIRST 26 sheets. Add a constant to change this, or add
aditional code in the loop if the sheets are index irregularly.

 

[Mike H]

Maybe

Sub sonic()
IntervalType = "d"
For x = 0 To 365 Step 14
mydate = Format(DateValue("January 1, 2008"), "mmm d")
mydate = Format(DateAdd(IntervalType, x, mydate), "mmm d")
For a = 1 To Worksheets.Count
Application.DisplayAlerts = False
If Left(Sheets(a).Name, 5) = mydate Then Sheets(a).Delete
Application.DisplayAlerts = False
Next
Next
End Sub

Mike

 

[Mike H]

typo

make second one
Application.DisplayAlerts = True

Mike