Macro to unhide sheets based on cell value (not sheet tab name but tab contents)

[HNoble]

Hi

I'm looking to see if there is a way to unhide sheets based on a cell value imputted on a main sheet, which then finds the cell value within other sheets that are hidden.

So I don't want to unhide the sheets based on their sheet tab name, but to find the cell value within the sheets to match and unhide them.

For example, the main sheet has a few search options (engineer name, project name etc.). Each sheet that is hidden has a table at the top of each page with this information. So if I was to enter the name Bob for the engineer, it would find every sheet that has Bob in the table and unhide them.

Hope that makes sense and that it's even possible.

Thanks in advance!

 

[Michael M]

A macro will do that, but I'd suggest you will need to unhide each sheet, do the search, if found stay open, otherwise rehide the sheet.
All this could be done without the user seeing the activity.
Is that something that would be suitable ??

 

[HNoble]

Hi Michael

Yes that makes sense, thank you! I hadn't thought about that. If you could help me with the macro that would be great!

Thanks!

 

[Michael M]

what is ther location of the table in each sheet ?
Is it an actual table or just rows at the top of the worksheet ?
Are there sheets that need to be excluded from the search ?

 

[HNoble]

The range for the table on each sheet is B2:E12. It is just rows at the top of the sheet formatted to look like a table.

Yes but I haven't created all of the yet. There is one sheet called "Template" that I want to exclude.

Thanks

 

[HNoble]

Hi Michael

I forgot the say earlier that the text I want to search with also comes from a table where they can enter different search criteria. They can only search by one thing for now so I guess it's working from a value within range (F9:F13).

Thanks again!

 

[Michael M]

This assumes the search word is in F9 on the template sheet !!

Sub MM1()
Dim ws As Worksheet, WRD As String, loc As Range
WRD = Sheets("Template").Range("F9").Value
For Each ws In Worksheets
    If ws.Name <> "Template" Then ws.Visible = xlSheetVisible
    With ws
        Set loc = .Range("B2:E12").Find(What:=WRD, SearchDirection:=xlNext)
        If loc Is Nothing Then ws.Visible = xlSheetHidden
    End With
  Next ws
End Sub

 

[HNoble]

That works perfectly, thank you very much!! One last question. Are you able to help with an error message if the text entered is not found in any of the sheets?

At the moment it is just debugging because it can't find anything.

Thanks

 

[HNoble]

No worries I figured out the error handling. Thank you for your help, much appreciated!

 

[Michael M]

So, that means it's trying to hide all sheets...Isn't the "Template" visible to start with ??