Ru;n a Routine on all Sheets Except 2

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Izran - 25 Jan 2006 19:13 GMT

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

Reply to this Message

Jim Thomlinson - 25 Jan 2006 19:35 GMT

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)...

Signature

HTH...

Jim Thomlinson

> 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
[quoted text clipped - 7 lines]
>
> Mike

Reply to this Message

Sprinks - 25 Jan 2006 19:35 GMT

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

Sprinks

> The only trick is you need to be able to identify the sheets, no matter what.
> Something like this should work for you...
[quoted text clipped - 25 lines]
> >
> > Mike

Reply to this Message

Izran - 25 Jan 2006 19:41 GMT

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

Regards,
MPManzi

Reply to this Message

Sprinks - 25 Jan 2006 19:35 GMT

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

> 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
[quoted text clipped - 7 lines]
>
> Mike

Reply to this Message