Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Sheets
Lets say i have a workbook with 20 sheets on it though the
number may vary and I want to delete all sheets which arent named "Control", "Menu" and "Table" How could i accomplish this with VBA? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Sheets
Jordan,
Try something like the following: Dim WS As Worksheet For Each WS In ThisWorkbook.Worksheets Select Case WS.Name Case "Control", "Menu", "Table" Case Else WS.Delete End Select Next WS -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Jordan" wrote in message ... Lets say i have a workbook with 20 sheets on it though the number may vary and I want to delete all sheets which arent named "Control", "Menu" and "Table" How could i accomplish this with VBA? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Sheets
Hi Jordan
similar to a post 2 hours ago try ---- Public Sub delete_sheets() Dim wkSht As Worksheet Application.DisplayAlerts = False For Each wkSht In Worksheets If Lcase(trim(wkSht.Name)) < "Control" and Lcase(trim(wkSht.Name)) _ < "Menu" Lcase(trim(wkSht.Name)) < "Table" Then wkSht.Delete End If Next wkSht Application.DisplayAlerts = True End Sub Frank Jordan wrote: Lets say i have a workbook with 20 sheets on it though the number may vary and I want to delete all sheets which arent named "Control", "Menu" and "Table" How could i accomplish this with VBA? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Sheets
Jordan,
A better version is below: Dim WS As Worksheet Application.DisplayAlerts = False For Each WS In ThisWorkbook.Worksheets Select Case WS.Name Case "Control", "Menu", "Table" Case Else WS.Delete End Select If ThisWorkbook.Worksheets.Count = 1 Then Exit For End If Next WS Application.DisplayAlerts = True -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Jordan" wrote in message ... Lets say i have a workbook with 20 sheets on it though the number may vary and I want to delete all sheets which arent named "Control", "Menu" and "Table" How could i accomplish this with VBA? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Sheets
Hi Jordan,
How was the jungle? Application.DisplayAlerts = False For Each sh In Activeworkbook.Worksheets If sh.Name < "Control" And sh.Name < "Menu" And _ sh.Name < "Table" Then sh.Delete End If Next sh Application.DisplayAlerts = True -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Jordan" wrote in message ... Lets say i have a workbook with 20 sheets on it though the number may vary and I want to delete all sheets which arent named "Control", "Menu" and "Table" How could i accomplish this with VBA? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
deleting sheets 2&3 | Excel Discussion (Misc queries) | |||
deleting sheets | Excel Discussion (Misc queries) | |||
Deleting All Sheets Other than specified from a workbook | Excel Programming | |||
Deleting sheets | Excel Programming | |||
deleting sheets | Excel Programming |