Hide tab based on tab name

[ashaw2]

Hi,
I already have a macro to change tab names automatically based on call values, any tabs that are ‘spare’ and not needed are ***** spare1 , spare2, spare3 and so on. I am looking for a macro to hide tabs which include ‘spare’ in there name if that’s possible if someone can help please?

[nigelog]

Please Login or Register  to view this content.

[ashaw2]

Hi, thanks for the quick reply! This works great, however it doesn't reshow tabs when the data is changed in the cell that dictates the tabs name, also is it possible to add it to my current button I've created so it runs after the code before it so that i dont have to add to buttons if that makes sense? I've just to make it work but have so far failed!
The code before is:

Please Login or Register  to view this content.

Also

[AliGW]

Administrative Note:

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. As you are still quite new, I will do it for you today.)

[Fluff13]

How about

Please Login or Register  to view this content.

[ashaw2]

Hi, Thanks for your reply. The code still works for changing the tab names based on value, but it doesn't seem to work for hiding any tabs with 'spare' in the name and vice versa it doesn't unhide the ones that don't. Any suggestions?

[Fluff13]

Did you change the code at all?

[ashaw2]

Hi, No i didnt change the code, Just copy and pasted it in. Ive just retried copying the code in and i am now getting a run time error 1004. method 'name' of object '_worksheet' failed

[Fluff13]

Can you please supply a workbook, that shows that behaviour.

[ashaw2]

Ive attached a sample. On the Store tab, the wheel is the button you click to update the tab names. These are based on the list of names to the right of this which autofilf automatically into H2 of each sheet.
I want this set up to stay but the spare tabs i would like to hide and then once a new member joins the team i put their name into the store tab column, press the update button and the sheet then appears again. This is the sheet iam working on
Thanks for looking at this for me

[Fluff13]

The code I supplied isn't even in your workbook.
The reason you got the RTE is that neither the Staff nor Store sheets have anything in H2, so try

Please Login or Register  to view this content.

[kvsrinivasamurthy]

This is worksheet event code with H1 in Sheet1 as reference cell.

Please Login or Register  to view this content.

To paste the code
Right click on Sheet tab --> view code
VB window opens.
Paste the code
Close the window.

[ashaw2]

Thanks for all your help Fluff13! It works now, thanks for ironing out the kinks! Sorry about the code missing from upload sheet i had to upload a very basic sheet that i quickly put together as i cant upload my actual sheet due to GDPR etc...

[Fluff13]

Glad to help & thanks for the feedback.

[kvsrinivasamurthy]

@ashaw2

Have you seen post#12