Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Worksheets
Rob,
I think you need to use "Or" rather than "And". An abbreviated version of your code is: If mySht.Name = "Cost Center Template" And mySht.Name = "ytd.srv.inv" Then This will ALWAYS return False because mySht.Name may be "Cost Center Template" or it may be "ytd.srv.inv", but it will NEVER be both "Cost Center Template" AND "ytd.srv.inv", so the "And" comparison will always return False. Also, as a matter of coding style, I would use a Select Case statement than a whole string of And comparisons. It will make your code much cleaner and easier to maintain. E.g., Dim mySht As Worksheet Application.DisplayAlerts = False For Each mySht In ActiveWorkbook.Worksheets Select Case mySht.Name Case "Cost Center Template", _ "ytd.srv.inv", _ "InvSrvTemplate" ' and all the other sheets ''''''''''''''''''''' ' Do nothing ''''''''''''''''''''' Case Else If ActiveWorkbook.Worksheets.Count 1 Then mySht.Delete End If End Select Next mySht Application.DisplayAlerts = True -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Rob" wrote in message ... I have another question pertainiing to this particular project. By the way, saving the file does help (lol). At the beginning of this monly project there are certain worksheets that need to be deleted. After all the calculations are verified there are certain sheets that need to be deleted. I am using the following code at the beginning: Sub DeleteCCWorksheets() Dim mySht As Worksheet Application.DisplayAlerts = False For Each mySht In ActiveWorkbook.Worksheets If mySht.Name < "Cost Center Template" And mySht.Name < "ytd.srv.inv" And mySht.Name < "InvSrvTemplate" And mySht.Name < "ytd.srv.detail" And mySht.Name < "InvSrvytdTemplate" And mySht.Name < "inv.finance" And mySht.Name < "inv.srv.detail" And mySht.Name < "patti detail 2" And mySht.Name < "dcd.detail" And mySht.Name < "recap" And mySht.Name < "patti detail" And mySht.Name < "detail by item" And mySht.Name < "invoice detail" Then mySht.Delete End If Next mySht Application.DisplayAlerts = True End Sub This code works fine. Here is the code that is to be used at the end: Sub DeleteNonMonth() Application.DisplayAlerts = False For Each mySht In ActiveWorkbook.Worksheets If mySht.Name = "Cost Center Template" And mySht.Name = "ytd.srv.inv" And mySht.Name = "InvSrvTemplate" And mySht.Name = "ytd.srv.detail" And mySht.Name = "InvSrvytdTemplate" And mySht.Name = "inv.srv.detail" And mySht.Name = "patti detail 2" And mySht.Name = "dcd.detail" And mySht.Name = "patti detail" And mySht.Name = "invoice detail" Then mySht.Delete End If Next mySht Application.DisplayAlerts = True End Sub This code does nothing and I don't get an error message. Will someone please help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting worksheets | Excel Discussion (Misc queries) | |||
Deleting WorkSheets MANY | Excel Programming | |||
Deleting Worksheets in VBA | Excel Programming | |||
Help deleting worksheets | Excel Discussion (Misc queries) | |||
Deleting worksheets | Excel Programming |