Check if Specific Visible Worksheet Exists

[BEEJAY]

Greetings All:

Have set up macro to do a process on all worksheets in workbooks (using Array)
However, if a ws in array doesn't exist, or is hidden, macro errors.
therefore, making another macro to check for that one ws only.
if must exist AND must be visible - then Activate sheet
if does NOT exist, then exit macro.
Most various methods found so far work, Except for when ws is hidden.
Detailed help would be greatly appreciated.

B.J.
Not the brightest bulb in the chandelier!

PS. I hope I'm doing this all correctly!

[StephenR]

Perhaps you could add an if statement to check first whether the worksheet is visible? What code are you using?

[royUK]

Here's two functions

Please Login or Register  to view this content.

[JBeaucaire]

It sounds like you may be making an even more basic mistake. In VBA you don't need to "activate" a ws to do work on it. If your macro is processing a bunch of ws doing things, you can do those things without ever activating them. This means they can be (and stay) hidden, too.

All you need to do is
1) not activate the sheet
2) preface the command with the ws variable name so the commands are properly "targeted".

Post your full code here that is currently "erroring out" when a sheet is hidden. Let's see if we can adapt that for you to not care whether a sheet is hidden or not. be sre to put code tags around your code, like Roy did above.

[BEEJAY]

Good Morning: Thanks for the responses so far.
Now that there are some experts looking at this, it just dawned on me that I probably should go back to the original code, using the array.
Code is exact as used, other than names changed to protect the innocent.

Please Login or Register  to view this content.

ALL of the w/s's specified, if exist, will be visible.
Works exactly as wanted when all sheets are available AND visible (which they should be)

Sh_3 is the only one that does NOT exist on all 16 Templates.
When trying to use this code on W/B's without Sh_3 get:
Run Time Error '9': Subscript out of Range.

If there is a way to get THIS code to operate as specified, I can eliminate the 'work-around' macro that I was initially asking about.

Thank-you
(PS: Pls. don't hesitate to wrap my knuckles if I'm not following proper procedure(s), etc. I'm tickled that I found a site that actually enforces basic rules and procedures. Makes it more pleasant for everyone!!)

[JBeaucaire]

If what you want to occur is "reset" each existing worksheet so that cell A1 is selected on each sheet, then what you're doing is exactly right. This IS one of those rare instances when selecting is necessary.

Putting non-existent sheetnames into an array the way you have is a syntax that WILL cause the loop to fail the first time through, even if we tell Excel to blow through the errors, that's too big an error. So let's move the sheet names out into a separate array we can reference without Excel complaining.

Please Login or Register  to view this content.

[BEEJAY]

Thanks so much.

One (likely) small situation. The following does not seem to work at all.

Please Login or Register  to view this content.

No error message comes up - It walks right thru that code, as if it is working.
Yet, each time I step thru the code, the last sheet showing is Sh_8.
I tried changing the code to Ubound, and it still ends up at Sh_8.

As I don't understand (yet) how LBound &UBound actually works, I don't understand how the coding "numbers" the sheets and how they relate to each other.
I thought it might have to do with the numbering as shown in Project-personal, where it shows that Sheet 1 is actually my Sh_4, Sheet 2 is actually my Sh_1, etc. However, that reasoning was also blown away.

As part of my learning curve, and to keep this macro at a 'higher' level, I'd rather not just resort to using my original code to accomplish that last step.

[JBeaucaire]

First, my apologies, use this:

Please Login or Register  to view this content.

ARRAYS. The array we created can be easily edited using the syntax shown.

Each item in an array can be referenced with the "index" number of the item in the array. But arrays don't index from 1-100+, they index from 0-100+. You can also index arrays manually with any numbers you want, but that's a lesson for another day.

Lbound(ShtArr) - "lower boundary" means the index number of the first item in the array. In this instance, it's probably equal to 0.

Ubound(ShtArr) - "upper boundary" means the index number of the last item in the array. In this instance, it's probably equal to 7.


The line of code above is meant to replace your original code, this version will activate the first sheet name in your array. This method, it doesn't matter what order sheets are in or if there are other sheets, this will activate the first sheet listed in your array.

[BEEJAY]

Jerry: Three big books and lots of time on internet did not come close to helping me understand what you just explained in a few lines.

Thank-You

[JBeaucaire]

Glad to help! We are a village.