Ru;n a Routine on all Sheets Except 2

[Izran]

Here is the situation. I am writing a routine to generater output
reports. I have a sheet with a template of the page setup and another
sheet that I am going to transfer that into. The users can import
multiple sheets of data. This routine needs to go through their sheets
to extrapolate the necessary information. So I need my routine to go
through every sheet except the template sheet and and output report
sheet. I guess I can set a flag up on those two sheets such that if it
reads the flag it goes onto the next sheet, but I wonder if there is a
better way.

Mike

[Jim Thomlinson]

The only trick is you need to be able to identify the sheets, no matter what.
Something like this should work for you...

dim wks as worksheet

for each wks in worksheets
select case wks.codename
case "Sheet1"
case "Sheet2"
case else
'Run your routine on wks
end select
next wks

Make sure you use the code name for the sheet as opposed to the name (Tab
name) which could be changed by the user. Code name is the one you see in the
VB Explorer Sheet1(TabName)...
--
HTH...

Jim Thomlinson


"Izran" wrote:

> Here is the situation. I am writing a routine to generater output
> reports. I have a sheet with a template of the page setup and another
> sheet that I am going to transfer that into. The users can import
> multiple sheets of data. This routine needs to go through their sheets
> to extrapolate the necessary information. So I need my routine to go
> through every sheet except the template sheet and and output report
> sheet. I guess I can set a flag up on those two sheets such that if it
> reads the flag it goes onto the next sheet, but I wonder if there is a
> better way.
>
> Mike
>
>

[Sprinks]

If the two sheets to exclude are named consistently, you can use that property:

Dim wrksht As Worksheet
For Each wrksht In ActiveWorkbook.Worksheets
If (wrksht.Name <> "Templates" AND wrksht.Name<>"OutputReport") Then
' Do Stuff here
End If
Next wrksht

Sprinks

"Izran" wrote:

> Here is the situation. I am writing a routine to generater output
> reports. I have a sheet with a template of the page setup and another
> sheet that I am going to transfer that into. The users can import
> multiple sheets of data. This routine needs to go through their sheets
> to extrapolate the necessary information. So I need my routine to go
> through every sheet except the template sheet and and output report
> sheet. I guess I can set a flag up on those two sheets such that if it
> reads the flag it goes onto the next sheet, but I wonder if there is a
> better way.
>
> Mike
>
>

[Sprinks]

Thanks, Jim. I was not aware of that property.

Sprinks

"Jim Thomlinson" wrote:

> The only trick is you need to be able to identify the sheets, no matter what.
> Something like this should work for you...
>
> dim wks as worksheet
>
> for each wks in worksheets
> select case wks.codename
> case "Sheet1"
> case "Sheet2"
> case else
> 'Run your routine on wks
> end select
> next wks
>
> Make sure you use the code name for the sheet as opposed to the name (Tab
> name) which could be changed by the user. Code name is the one you see in the
> VB Explorer Sheet1(TabName)...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Izran" wrote:
>
> > Here is the situation. I am writing a routine to generater output
> > reports. I have a sheet with a template of the page setup and another
> > sheet that I am going to transfer that into. The users can import
> > multiple sheets of data. This routine needs to go through their sheets
> > to extrapolate the necessary information. So I need my routine to go
> > through every sheet except the template sheet and and output report
> > sheet. I guess I can set a flag up on those two sheets such that if it
> > reads the flag it goes onto the next sheet, but I wonder if there is a
> > better way.
> >
> > Mike
> >
> >

[Izran]

Thank you very much. It's always the simple things that can trip you
up.......

Regards,
MPManzi