flagging a sheet

[bultimatem]

In XL97, I have a workbook that contains a few sheets, and a few charts. In my sheets I have 3 or 4 special sheets and I need to find a way to loop through those sheets only. I'd like to set some sort of a flag to mark those sheets as special. I've tried creating a named range that will only exist in those workbooks but that creates problems because XL doesn't seem to like having a named range in more than one workbook.

So in conclusion, I'd like to be able to loop through all of the sheets in my workbook, and only perform an operation on those that are of that special type. I need a way to differentiate those sheets from the other sheets in my workbook.

If anyone has any ideas, I'd really really appreciate it.

Thanks in advance,

Bryan Marble
IEWS
BAE Systems

 

[Luis939]

For i = 1 To ActiveWorkbook.Sheets.Count

If ActiveWorkbook.Sheets(i).Range(.....

'Conditional statements to check

End If
Next i

Note: activeworkbook can be thisworkbook or Workbooks("filename.xls&quot.....

I hope that helps, or at least can get you started, since this is code i got from one of the threads i started, so take a look at it
thread707-567280 me how it goes

 

[bultimatem]

Alright, I changed the implementation so that each sheet, on activation is given a range with the specified flag name, which works, because I just needed to know if an active sheet was of that type.

Thanks for the help

Bryan Marble
IEWS
BAE Systems

 

[TonyJollans]

Hi bultimatem,

I assume you have mixed up workSHEETs and workBOOKs in your post - there is nothing to stop you having the same Range Name in different workbooks, but I think you want them in different sheets in the same workbook.

Anyway, this is a bit of a kludge but you could add a function to each of the sheets you are interested in and check its returned value. The sheets which didn't have the function would give an error which you have to trap.

In the code module for each special sheet put:

Function Special() As Boolean
Special = True
End Function

Then in your code module put:

Dim w As Integer
Dim Special As Boolean
For w = 1 To ActiveWorkbook.Sheets.Count
    Special = False
    On Error Resume Next
    Special = Sheets(w).Special
    If Special Then

' Do your stuff here

    End If
Next

Enjoy,
Tony

 

[SkipVought]

Hi,

You can either designate something special OR just use the Sheet Name as follows...

For Each WorkSheet in ActiveWorkbook.WorkSheets
   Select Case WorkSheet.Name
      Case "Special Sheet 1", "Another One", "And Yet A Third"
         'loop thru these sheets only

   End Select
Next

Hope this gives you another potion.


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com