Numbering of worksheets

[brian.jermain]

I have a workbook with a number of sheets all individually named.

I am trying to loop through most of them in VBA by using something
along the lines of:-

for a=5 to 16
sheet(a).select
rest of code

Next

My problem stems from the fact that I have no idea how the sheet
numbering system used in VBA relates to the sheet numbers in the
workbook or the order they are displayed in the workbook. I have three
or four sheets that I wish to exclude as the information required is
collated on these sheets and I am constantly adding and removing sheets
as projects are completed. It would probably suit me if the "static"
sheets could remain sheet 1 to sheet 4 and then loop round sheet 5 to
the end

My question is how do I know at any one time which sheets to loop
through - as I have already said they are named but I am having
difficulty identifying the sheet numbers at any particular time

Sorry if I am not making myself clear - it really should be a simple
problem but I cannot get to the bottom of it

Brian
Scotland

 

[Mark Lincoln]

The sheets are indexed from left to right. If you always want to
search the same four sheets, make certain they are the first four and
set your index to 1 to 4. Any new sheets should be placed to the right
of the last sheet you wish to search.

If the sheets you wish to search always have the same names, you can
access them by name.

 

[Dave Peterson]

How about just looping through all the sheets and ignoring the ones you don't
want...


Option Explicit
sub testme01()
dim wks as worksheet
for each wks in activeworkbook.worksheets
select case lcase(wks.name)
case is = "sheet1","sheet5","anothersheet","one more"
'do nothing, skip it.
case else
'your real code
end select
next wks
end sub