Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Delete blanks between a range and populate only the names inthe given range | Excel Discussion (Misc queries) | |||
Looping through selected worksheets and delete a range (e.g., A2:D15) | Excel Programming | |||
How to Delete a Range in Closed Workbook (to Replace Delete Query) | Excel Discussion (Misc queries) | |||
not delete worksheets from names in a range | Excel Discussion (Misc queries) | |||
Range.Delete and Range.Resize.Name performance issues | Excel Programming |