Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a collection of visible worksheets
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!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a collection of visible worksheets
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" wrote in message ... 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!!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a collection of visible worksheets
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!!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a collection of visible worksheets
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!!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a collection of visible worksheets
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" wrote in message ... 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!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a collection in a class | Excel Programming | |||
creating a new collection | Excel Programming | |||
When creating a formula how do you select only visible cells | Excel Discussion (Misc queries) | |||
creating a collection of Sheets references | Excel Programming | |||
worksheets collection... find... | Excel Programming |