apply a macro to all sheets except for a certain sheet
 

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


--
minrufeng
------------------------------------------------------------------------
minrufeng's Profile: http://www.excelforum.com/member.php...o&userid=26208
View this thread: http://www.excelforum.com/showthread...hreadid=515068

apply a macro to all sheets except for a certain sheet
 

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
--
Kevin Backmann


"minrufeng" wrote:


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


--
minrufeng
------------------------------------------------------------------------
minrufeng's Profile: http://www.excelforum.com/member.php...o&userid=26208
View this thread: http://www.excelforum.com/showthread...hreadid=515068

apply a macro to all sheets except for a certain sheet
 

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

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.

minrufeng wrote:

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

--
minrufeng
------------------------------------------------------------------------
minrufeng's Profile: http://www.excelforum.com/member.php...o&userid=26208
View this thread: http://www.excelforum.com/showthread...hreadid=515068

--

Dave Peterson

apply a macro to all sheets except for a certain sheet
 

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" wrote:

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

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.

minrufeng wrote:

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

--
minrufeng
------------------------------------------------------------------------
minrufeng's Profile: http://www.excelforum.com/member.php...o&userid=26208
View this thread: http://www.excelforum.com/showthread...hreadid=515068

--

Dave Peterson

apply a macro to all sheets except for a certain sheet
 

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.



peterparker wrote:

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" wrote:

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

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.

minrufeng wrote:

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

--
minrufeng
------------------------------------------------------------------------
minrufeng's Profile: http://www.excelforum.com/member.php...o&userid=26208
View this thread: http://www.excelforum.com/showthread...hreadid=515068

--

Dave Peterson


--

Dave Peterson