Macro to Unhide sheets based on input box text

[Lehcar]

Morning,

I am a novice to VBA, anything I have ever written is self-taught so be warned!

I have searched and tried numerous ways of writing VBA code to achieve this but to no avail.

I have an inherited 28 spreadsheets each with hundreds of sheet tabs some hidden and some not.

Every month I have to find any sheets containing part of the months name and unhide it.

So sheet name examples "RAMS rejected -MAY", "RAMS rejected - JUNE", "Open Permits - MAY", "Open Permits - JUN"

I want an input box to appear to ask them for first 3 letters of month name - i.e. "JUN"
and then using this input search all .xlhidden sheets (there are no 'veryhidden' ones) and make them xlvisible.

So in the above example it would unhide: "RAMS rejected - JUNE", "Open Permits - JUN"
and leave the other two hidden

I can't even start to give examples of the codes tried as I have tried so many that my brain is now mush !!

Thank you in advance.

Oh yes - I am using Excel 2013 in case this matters.

 

[slinky]

This should do the job for you..

Sub UnhideSheets()
Dim sh As Worksheet
Application.ScreenUpdating = False
Dim Search As String
Search = InputBox("Enter Search Criteria", "Sheet Finder")
For Each sh In ActiveWorkbook.Sheets
If InStr(1, sh.Name, Search) > 0 Then sh.Visible = xlSheetVisible
Next
Application.ScreenUpdating = True
End Sub

 

[RoryA]

Hi, and welcome to the forum.

Try something like this:

Sub UnhideThem()
   Dim sMonth As String
   Dim sht As Object
   sMonth = InputBox("Enter first three letters of month to unhide")
   If Len(sMonth) > 0 Then
      For Each sht In ActiveWorkbook.Sheets
         If sht.Visible = xlSheetHidden Then
            If UCase$(sht.Name) Like "*" & Left$(sMonth, 3) & "*" Then sht.Visible = xlSheetVisible
         End If
      Next sht
   End If
End Sub

 

[Lehcar]

Thank you slinky and RoryA - both worked a treat - now for my lesson of the day ... try and understand what you both wrote so I can work out where I was going wrong so I learn for the future.

Sorry it took me so long to test - bloomin' meetings!!

Cheers