SearchBar Preferances

[Adamguk]

Hi All,

I am working on a huge workbook that is controlled by various userforms controlling different worksheets. I want to place on my main userform a search option whereby users can enter a term for example "Project X" and wherever "Project X" appears in any of the sheets the rows are copied and pasted into a new sheet for them. The main blocker to this i have discovered is all my worksheets are hidden with the exception of the worksheet containing the userform. I have seen various variations to the search macro, but cannot find one that searches in 'hidden' sheets?

Any help appreciated.

 

[Andrew Poulsom]

Welcome to MrExcel.

This worked for me if Sheet2 was hidden and one of the cells on it contained Testing:

Sub Test()
    Dim Rng As Range
    With Worksheets("Sheet2")
        Set Rng = .Cells.Find(What:="Testing", After:=Cells(1, 1), LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
    End With
    If Not Rng Is Nothing Then
        MsgBox "Found in " & Rng.Address(False, False) & " on sheet " & Rng.Parent.Name
    End If
End Sub

Of course you can't activate a hidden sheet or a cell on it, but that shouldn't be necessary.

 

[Adamguk]

only problem being that macro works on 1 x sheets only. I need one to work through almost 50 sheets

 

[Andrew Poulsom]

You can loop around the worksheets. My example was only to show you that activating the sheet wasn't necessary.