apply a macro to all sheets except for a certain sheet

M

minrufeng

If I want to apply a macro to all sheets except for a certain sheet, say
sheet "source data", what should I do? Thank you for your help. below is
my current code. Thank you for your help!

Public Sub insertrowinallsheets()
Dim sheet As Variant
For Each sheet In ActiveWorkbook.Sheets
sheet.Cells.Find("CostClub").EntireRow.Insert
Next sheet
End Sub
 
K

Kevin B

The following code snippet with give you the general idea of how to do this.
You just loop through all the sheets in the workbook and check the name of
each sheet as you go.

Sub DoSomething()

Dim wb As Workbook
Dim ws As Worksheet
Dim strName As String

Set wb = ActiveWorkbook

For Each ws In wb.Worksheets
strName = ws.Name
If strName <> "Sheet1" Then
'Do some thing here
End If
Next ws

Set wb = Nothing

End Sub
 
D

Dave Peterson

And if that list of worksheets gets longer, sometimes it's easier to see in the
Select case structure:

Sub DoSomething2()

Dim wb As Workbook
Dim ws As Worksheet

Set wb = ActiveWorkbook

For Each ws In wb.Worksheets
select case lcase(ws.name)
case is = "source data", "anothersheet", "evenmore"
'do nothing
case else
'Do some thing here
End select
Next ws

Set wb = Nothing

End Sub

Make sure you type that list in lower case--that's what the select case is
looking at.
 
P

peterparker

could you just use a IF statement and check the activeworksheets name
property. If it matches the name you do not want to have the macro attached
do nothing.
Dave Peterson said:
And if that list of worksheets gets longer, sometimes it's easier to see in the
Select case structure:

Sub DoSomething2()

Dim wb As Workbook
Dim ws As Worksheet

Set wb = ActiveWorkbook

For Each ws In wb.Worksheets
select case lcase(ws.name)
case is = "source data", "anothersheet", "evenmore"
'do nothing
case else
'Do some thing here
End select
Next ws

Set wb = Nothing

End Sub

Make sure you type that list in lower case--that's what the select case is
looking at.
Click to expand...
 
D

Dave Peterson

When that list of worksheets gets long, I find that the select case structure
easier to read than a bunch of if/then/else statements.


could you just use a IF statement and check the activeworksheets name
property. If it matches the name you do not want to have the macro attached
do nothing.
Click to expand...
 
Top