Macro/VBA to allow Filter based on cell value from different Tab

[Jzfbkr]

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]INPUT[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Fixture[/TD]
[TD]HS[/TD]
[TD]AS[/TD]
[/TR]
[TR]
[TD]TEAM A v TEAM B
[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]TEAM C v TEAM D[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]TEAM E v TEAM F[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]TEAM A v TEAM C[/TD]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


Im looking to try and write a macro to autofilter a table based on the contents on the cell above. This cell is on a different sheet to the table I am looking to filter.

Ideally the I would like to return the following:

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]TEAM A[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Fixtures[/TD]
[TD]HS[/TD]
[TD]AS[/TD]
[/TR]
[TR]
[TD]TEAM A v TEAM B[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]TEAM A v TEAM C[/TD]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


My range is alot larger using 365R x 225C

Any help with this would be massively appreciated as I am very new to macros.

Thanks

 

[Gavin T]

Try this:

Sub FilterTeams()

    Dim LastRow As Long

    LastRow = Range("A" & Rows.Count).End(xlUp).Row

    ActiveSheet.Range("A3:C" & LastRow).AutoFilter Field:=1, Criteria1:="=*" & Range("A1") & "*"

End Sub

This is assuming that your data starts from range A3 and your Input cell is A1.

 

[Jzfbkr]

Excellent! I wont begin to question how it works as I know it will go right over my head.

Where you have put range will i need to substitute for the whole range including column headers or is it just the data. Also is there a way to filter rows that Contain the word/s in the input cell?

I realise you have already helped immensely and its rather cheeky of me to ask for more.

Thank you ever so much

 

[Gavin T]

...Where you have put range will i need to substitute for the whole range including column headers or is it just the data...

You would include the headers as well as the data.

...Also is there a way to filter rows that Contain the word/s in the input cell?

Isn't that what it's currently doing?

...Thank you ever so much

I'm glad I could help!

 

[Jzfbkr]

I am still a little confused...

The input cell is in a sheet called 'Print Out Report' in a merged cell F4:V5

The column I would like to filter is based in a different sheet called 'GOALS ANALYSIS 2.0' cells C4:368 but the range of data I would like it applied to occupies A4:HQ368 on the same sheet.

It sounds like abit of a riddle. Again I'd really appreciate the help, I appreciate your probably very busy but this would make life very easy.

Thanks again.

 

[Gavin T]

Perhaps this:

Sub Macro1()

    Dim sInput  As String

    sInput = Sheets("Print Out Report").Range("F4").Value

    Sheets("GOALS ANALYSIS 2.0").Range("A4:HQ368").AutoFilter Field:=3, Criteria1:="=*" & sInput & "*"

End Sub

 

[Jzfbkr]

Thanks for the sharp reply. I've added this as a module but unfortunatly it doesn't seem to work.

Thanks for all your help on this one. I'll try and think of another way around this issue.

 

[Gavin T]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$F$4" Then
        Sheets("GOALS ANALYSIS 2.0").Range("A4:HQ368").AutoFilter Field:=3, Criteria1:="*" & Target & "*"
    End If

End Sub

Put this code in the code module of the sheet with the input cell.

 

[Jzfbkr]

That has worked a treat! Thank you ever so much Gavin appreciate you taking the time to help twice!!

 

[Gavin T]

You're welcome.