Macro search entire workbook

Please see attached example

I have several worksheets with the format mmm yyyy in which I want to search for an account name. Very similar to :
http://www.excelforum.com/excel-prog...readsheet.html

I want it to be able to search for a partial hit as well ideally. My macro would be slightly different to the above as I am searching many worksheets, which will be ever growing.
I need the macro to return the account as well as returning the name of the worksheet tab that it resides in.

For example if I type "Char" I should get two hits for Charoen so it looks like this

Account Renewal Month
Charoen Jan-12search.xls
Charoen Feb-12

thanks, yes I can see the difference between the filter of a list and a search, but I do need to have a way to display not just the name i am searching for but accompanying information belonging to it in adjacent rows.

I am perfectly aware of the Find function but it doesnt replicate what I want to see in a search. It just gives me a list of where my cell ranges are - it doesnt give me any other information that is attributable to the cell in question.

hi nickmax1, please check attachment, hope that helps

watersev,
thanks a lot but its not quite working.

I should have deleted the Source colomn as that is complicating matters, but your search macro is finding the right account name but it is putting it in the header "Renewal Month/Year" instead of the "Account" header.
Under "Renewal Month/Year" I was hoping the macro would return the name of the worksheet the data resides in.

?

please check attachment

you sir, are a legend.

Is the SEARCH button necessary, can the macro be run when something is entered in the search box? no matter if not!

it can but I would stick with this option. If you insist it can be done that way

thats fine...

I have entered your code into my "master" spreadsheet but now i get an autofilter error??

Activity test.xlsm

watersev you are some kind of a genius.

thank you so much. rep added.

Watersev you kindly helped me with a search macro that looked through my workbook....I dont know what has changed but it doesnt work anymore..... It returns the right result but also returns other data???

Activity test.xlsm

if you change data layout be prepared to amend the code as well

sorry watersev that didnt quite work, still returns random entries (as well as the correct ones)

I do not get any mistaken data as a result of the search

that is strange....but if select "christ" i get Christchurch and 7 other entries.....

please check attachment, it should be ok now

fantastic!! not sure how you did it but this works - thank you so much

watersev,

A very small thing, but before the macro is run rows 17 on all sheets (except start, template, brokers, summary) are hidden....after the macro is run it unhides row 17 from the other sheets. Any way we can stop that from happening???

Sub WheresmyAccount()

Dim lrow As Long, search_val As String, mainsh As Worksheet, Sh, acclrow As Long, renewal_row, iflag As Boolean

lrow = Cells(Rows.Count, 2).End(xlUp).Row
search_val = Range("a3")

Application.ScreenUpdating = 0
Application.EnableEvents = 0

If lrow > 8 Then Range("b9", Cells(lrow, 3)).ClearContents
If Range("a3") = "" Then Exit Sub

Set mainsh = Sheets("Start")

For Each Sh In ActiveWorkbook.Sheets
    If InStr("GraphsStartSummary", Sh.Name) = 0 Then
        If Sh.Visible = True Then
            If Sh.Range("a17").EntireRow.Hidden = True Then iflag = True Else iflag = False
            With Sh.Range("c16", Sh.Cells(Rows.Count, "c").End(xlUp))
                .AutoFilter 1, "*" & search_val & "*"
                .Offset(1).Copy mainsh.Cells(Rows.Count, 2).End(xlUp).Offset(1)
                acclrow = mainsh.Cells(Rows.Count, 2).End(xlUp).Row
                renewal_lrow = mainsh.Cells(Rows.Count, 3).End(xlUp).Offset(1).Row
                If acclrow >= renewal_lrow Then
                    With mainsh.Range("c" & renewal_lrow & ":c" & acclrow)
                        .NumberFormat = "@"
                        .Value = Sh.Name
                    End With
                End If
                .AutoFilter
                If iflag = True Then Sh.Range("a17").EntireRow.Hidden = True
            End With
        End If
    End If
Next

Application.EnableEvents = 1
Application.ScreenUpdating = 1

End Sub

i havent gotten round to testing this on my master spreadsheet but based on the test file it is absolutely perfect....thank you so much!!

perfect. Its quite a different code to your original, but however it does it , it does it very very well. thanks again!

Watersev,

Your code (as always) runs brilliantly. I was wondering if there was any way I can identify the cells in which the results come from and in the next cell to the hyperlink have the output say:

If the hyperlinked cell is greater than 190 then: "This is an AP/RP"
Otherwise say "Normal Premium"

This I would imagine could be done without a macro but i dont know of a formula that can "read" the hyperlink and extract the cell address to come up with that...