Delete range of Worksheets
 

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

Delete range of Worksheets
 

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
--
HTH,
Barb Reinhardt



"WLMPilot" wrote:

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

Delete range of Worksheets
 

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.

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"WLMPilot" wrote:

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

Delete range of Worksheets
 

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

"WLMPilot" wrote:

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

Delete range of Worksheets
 

typo

make second one
Application.DisplayAlerts = True

Mike
"Mike H" wrote:

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

"WLMPilot" wrote:

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