Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case not recognized in Select Case
I am using Select Case in a macro that deletes worksheets to skip
specifically named worksheets. Code is: For Each Worksheet In Worksheets Select Case Worksheet.name Case "Sheet1", 'Sheet2", "Sheet3", etc. Case Else Worksheet.Activate Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True End Select Next Worksheet The problem is I have a worksheet named "BGE" that I want skipped and the select case is not recognizing it and deletes it. I wonder if this has to do with Excel 2k7 using BGE as a cell value. Any ideas? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case not recognized in Select Case
On Mon, 29 Mar 2010 08:52:02 -0700, kevlarmcc
wrote: I am using Select Case in a macro that deletes worksheets to skip specifically named worksheets. Code is: For Each Worksheet In Worksheets Select Case Worksheet.name Case "Sheet1", 'Sheet2", "Sheet3", etc. Case Else Worksheet.Activate Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True End Select Next Worksheet The problem is I have a worksheet named "BGE" that I want skipped and the select case is not recognizing it and deletes it. I wonder if this has to do with Excel 2k7 using BGE as a cell value. Any ideas? BGE is not in your Case statement; so when Worksheet.Name = BGE, the case else code will get executed, and the sheet will be deleted. --ron |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case not recognized in Select Case
You have 'Sheet2" instead of "Sheet2". Could that be it?
-- HTH, Barb Reinhardt "kevlarmcc" wrote: I am using Select Case in a macro that deletes worksheets to skip specifically named worksheets. Code is: For Each Worksheet In Worksheets Select Case Worksheet.name Case "Sheet1", 'Sheet2", "Sheet3", etc. Case Else Worksheet.Activate Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True End Select Next Worksheet The problem is I have a worksheet named "BGE" that I want skipped and the select case is not recognizing it and deletes it. I wonder if this has to do with Excel 2k7 using BGE as a cell value. Any ideas? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case not recognized in Select Case
You are using ActiveSheet.Delete which will always delete that sheet
that is active in Excel. It is NOT the sheet that is iterated by the For Each loop. You need to either select that sheet, (Worksheet.Select) or use the For Each variable (Worksheet.Delete). It is a common misconception that a For Each loop activates the ActiveSheet or ActiveCell. Such is not the case. If you expect a sheet or cell to be active, you must activate it yourself. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Mon, 29 Mar 2010 08:52:02 -0700, kevlarmcc wrote: I am using Select Case in a macro that deletes worksheets to skip specifically named worksheets. Code is: For Each Worksheet In Worksheets Select Case Worksheet.name Case "Sheet1", 'Sheet2", "Sheet3", etc. Case Else Worksheet.Activate Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True End Select Next Worksheet The problem is I have a worksheet named "BGE" that I want skipped and the select case is not recognizing it and deletes it. I wonder if this has to do with Excel 2k7 using BGE as a cell value. Any ideas? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case not recognized in Select Case
Hi Chip,
I think you are looking for something like: Sub DeleteSheets() Dim sht As Worksheet For Each sht In ActiveWorkbook.Sheets Select Case sht.Name Case "Sheet1", "Sheet2", "BGE" ' Complete list of sheets to preserve Case Else Application.DisplayAlerts = False sht.Delete Application.DisplayAlerts = True End Select Next End Sub HTH, Wouter |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change data of entire column from small case to upper case | Excel Worksheet Functions | |||
Case without Select Case error problem | Excel Discussion (Misc queries) | |||
Changing multiple cell text from lower case to upper case | Excel Discussion (Misc queries) | |||
Change the text from lower case to upper case in an Excel work boo | Excel Discussion (Misc queries) |