Home |
Search |
Today's Posts |
#1
|
|||
|
|||
For Each wks In ActiveWorkbook.Worksheets bar a specific one?
I have a macro that selects all worksheets in a book and undertakes
formatting for them. Is there a way that I can specify in the loop to select all sheets bar a specific one, which is called MAIN? If not then I intend to delete the sheet during the execution of a macro, but I need to get the macro to delete it without giving the user the option to confirm the delete (i.e. by pass the delete confirmation message). Any help would be appreciated. Thanks |
#2
|
|||
|
|||
Hi, Pank,
Try this to delete your sheet - the ENTER simulates you hitting the Enter key when the delete confirmation dialog (default button OK) is displayed. Cheers Pete Sub SheetDelete() SendKeys ("{ENTER}") Sheets("Sheet3").Delete End Sub "Pank Mehta" wrote: I have a macro that selects all worksheets in a book and undertakes formatting for them. Is there a way that I can specify in the loop to select all sheets bar a specific one, which is called MAIN? If not then I intend to delete the sheet during the execution of a macro, but I need to get the macro to delete it without giving the user the option to confirm the delete (i.e. by pass the delete confirmation message). Any help would be appreciated. Thanks |
#3
|
|||
|
|||
On Wed, 30 Mar 2005 00:31:03 -0800, "Pank Mehta"
wrote in microsoft.public.excel.misc: I have a macro that selects all worksheets in a book and undertakes formatting for them. Is there a way that I can specify in the loop to select all sheets bar a specific one, which is called MAIN? If not then I intend to delete the sheet during the execution of a macro, but I need to get the macro to delete it without giving the user the option to confirm the delete (i.e. by pass the delete confirmation message). Maybe I'm missing something, but doesn't this do what you want? Dim mySheet As Worksheet For Each mySheet In Worksheets If mySheet.Name < "MAIN" Then ' Do your formatting End If Next mySheet -- Michael Bednarek http://mbednarek.com/ "POST NO BILLS" |
#4
|
|||
|
|||
On Wed, 30 Mar 2005 00:53:01 -0800, Peter Rooney
wrote in microsoft.public.excel.misc: Try this to delete your sheet - the ENTER simulates you hitting the Enter key when the delete confirmation dialog (default button OK) is displayed. Cheers Pete Sub SheetDelete() SendKeys ("{ENTER}") Sheets("Sheet3").Delete End Sub Why not: Application.DisplayAlerts = False Sheets("Sheet3").Delete Application.DisplayAlerts = True SendKeys is always a kludge. "Pank Mehta" wrote: I have a macro that selects all worksheets in a book and undertakes formatting for them. Is there a way that I can specify in the loop to select all sheets bar a specific one, which is called MAIN? If not then I intend to delete the sheet during the execution of a macro, but I need to get the macro to delete it without giving the user the option to confirm the delete (i.e. by pass the delete confirmation message). -- Michael Bednarek http://mbednarek.com/ "POST NO BILLS" |
#5
|
|||
|
|||
Thanks to Peter and Michael for you help.
"Michael Bednarek" wrote: On Wed, 30 Mar 2005 00:31:03 -0800, "Pank Mehta" wrote in microsoft.public.excel.misc: I have a macro that selects all worksheets in a book and undertakes formatting for them. Is there a way that I can specify in the loop to select all sheets bar a specific one, which is called MAIN? If not then I intend to delete the sheet during the execution of a macro, but I need to get the macro to delete it without giving the user the option to confirm the delete (i.e. by pass the delete confirmation message). Maybe I'm missing something, but doesn't this do what you want? Dim mySheet As Worksheet For Each mySheet In Worksheets If mySheet.Name < "MAIN" Then ' Do your formatting End If Next mySheet -- Michael Bednarek http://mbednarek.com/ "POST NO BILLS" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|