Autofilter macro

Hi Everyone,

I have spreadsheet that has two tabs. One the first tab, entitled 'Dashboard', I have the text along the lines of 'Search for a name here'

On the second tab, entitled 'Team List' I have the details of the team, eg name, address, emergency contact etc.

I recorded the following macro:-

Sub Search()
'
' Search
'

'
    
    Sheets("Dashboard").Select
    Range("B4").Select
    Selection.Copy
    Sheets("Team List").Select
    ActiveSheet.Range("$A$1:$X$397").AutoFilter Field:=13, Criteria1:=Array( _
        "Bob Jones", _
        "David Jones", _
        "Sally Jones"), Operator:=xlFilterValues
        
End Sub

What I want to do is type in 'Jones' into the search area, the marco to copy and paste that ino the autofilter, and all of the names with 'Jones' be displayed.

The macro above works great for this, but if I enter 'Smith' it still displays the details for 'Jones'.

I think its because of the "Bob Jones", - "David Jones",- "Sally Jones") bit, but I dont know what the generic script for this would be.

Can anyone help?

Thanks
Alex

Hi, Thank you for your help so far, unfortunately the code supplied by Tigeravatar didnt work work, although your help is appreciated. The debugger wouldnt go past the range section but Im not sure why not.

I have attached a VERY basic version of my file. Please dont think that my excel training has been that simple!

Thanks for your help
Alex

Hi Watersev, this is fantastic and also answers my question! Just one thing, can I keep the hyperling associated with the original cell?
thanks

Hi,

Thanks for all your help. Im sorry its taken so long to get back, Ive been away from work for a few days.

The file attached above says that it corrupt when I try to open it. Am I doing something wrong when opening? Ive tried to save it first with the same result.

Would you be able to post the code by any chance?

Thanks again
Alex

checked the attached file, works normally

Private Sub test()

Dim TL As Worksheet

Application.ScreenUpdating = 0

ActiveSheet.UsedRange.Offset(4).ClearContents

If Range("b5") = "" Then Exit Sub

Set TL = Sheets("Team List")
If TL.Cells(Rows.Count, 1).End(xlUp).Row = 1 Then Exit Sub

With TL.UsedRange
    
    TL.AutoFilterMode = 0
    .AutoFilter 2, "*" & Range("b5") & "*"

    If .Cells(Rows.Count, 2).End(xlUp).Row > 1 Then
        .Copy
        Range("b7").PasteSpecial xlPasteValues
    End If
    
    .AutoFilter
    Range("b7").Select
    
End With

End Sub

Wow! Thats fantastic!

Thank you so much,
Alex

Briliant, thanks Watersev. Ill try it straight away.