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.


"Rich" <(E-Mail Removed)> wrote in message
news:1F3F1EFA-991B-4B08-BC15-(E-Mail Removed)...
>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!!!

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

Jim Thomlinson


"Rich" wrote:

> 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!!!

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!

"Jim Thomlinson" wrote:

> 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.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Rich" wrote:
>
> > 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!!!

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.

"JLGWhiz" wrote:

> 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.
>
>
> "Rich" <(E-Mail Removed)> wrote in message
> news:1F3F1EFA-991B-4B08-BC15-(E-Mail Removed)...
> >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!!!
>
>
>