Creating a collection of visible worksheets

  • Thread starter Thread starter Rich
  • Start date Start date
R

Rich

I need to search for data contained on certain worksheets. My current method
is this:

dim wks as worksheet
For Each wks in ThisWorkbook.Worksheets
if wks.name like "Data (*)" and wks.visible = xlSheetVisible
***now I collect my data***
......

My question is....
I have quite a few pages (15 to 18 min...maybe more) that are hidden, but I
have to cycle thru them because they are part of "ThisWorkbook". Although
this really does not take much time, I'd like to have a cleaner way. Is
there a simple way of creating a collection of only the VISIBLE worksheets in
the ThisWorkbook - thus reducing the number of worksheets I need to iterate
thru in the "For Each" loop??

Taking it a step further....creating a collection of visible worksheets
whose name matches the criteria I define??

Thanks!!!
 
Your question is a little confusing, since the code you posted does
eliminate the hidden sheets. It probably takes a gigasecond for the
compiler to see that a sheet is not visible with the code currently used.
 
Can it be done... Absolutely. Should it be done... Probably not. Unless you
need to itterate through these sheets a whole pile of times or other
procedures need to use these same sheets then there will be more overhead in
creating the collection than there is in your existing code.
 
Great! Thanks, guys!!
Being a bit of a rookie at this level of programming, I wanted to make sure
I was not taking the scenic path to the end result instead of the direct path.

Thanks for the replies!
 
Thanks. I was just trying to make sure that I was doing this in the most
efficient way - for execution time, code writing and code organization.
 
Back
Top