Macro using Cell value in filter

[jonashvid]

Hey I have this Macro:

Sub indbyrdes()

Sheets("Premier League").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$E$1521").AutoFilter Field:=2, Criteria1:="Liverpool"
ActiveSheet.Range("$A$1:$E$1521").AutoFilter Field:=5, Criteria1:="Arsenal"
Range("A301:E1300").Select
Selection.Copy
Sheets("Database").Select
Range("C12").Select
ActiveSheet.Paste
Columns("C:C").EntireColumn.AutoFit
Range("C17").Select
End Sub

How can I replace Criteria1:"Liverpool" with a reference to a string in a specific cell. For example if I type "Blackburn" in a cell then this value is used instead of "Liverpool"..?

 

[wigi]

Depending on a number of assumptions, this can work nicely:

Sub indbyrdes()

    With Sheets("Premier League").UsedRange
        .AutoFilter 2, [Database!A1]
        .AutoFilter 5, [Database!A2]
        .Offset(1).SpecialCells(xlCellTypeVisible).Copy [Database!C12]
        Sheets("Database").Columns(3).AutoFit
        .AutoFilter
    End With

End Sub

Cells A1 and A2 on the sheet Database contain the team names to be used in the filter.

Wigi

 

[jonashvid]

It works great, thx.