For Each wks In ActiveWorkbook.Worksheets bar a specific one?

P

Pank Mehta

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
 
P

Peter Rooney

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
 
M

Michael Bednarek

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).
Click to expand...

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
 
M

Michael Bednarek

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
Click to expand...

Why not:

Application.DisplayAlerts = False
Sheets("Sheet3").Delete
Application.DisplayAlerts = True

SendKeys is always a kludge.
 
P

Pank Mehta

Thanks to Peter and Michael for you help.

Michael Bednarek said:
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
Click to expand...
 
Top