I am trying to filter a sheet based on the named cell ("TeamData) on an
alternate sheet but keep getting a debug 'Object required' at the criteria
range. The debug recognises the value in the range as when hovering it shows
the correct text on the highlighted issue.

Detailed is the code:

Sub FilterSelection()

Sheets("Edit").Select

...AutoFilterMode =False

Columns("A:I").Select
Selection.AutoFilter
My_Range.AutoFilter Field:=1, Criteria1:="=" & Range("TeamData").Value
My_Range.AutoFilter Field:=2, Criteria1:="=" & Range("TeamData").Value

Range("A2:I500").Select
Selection.Copy
Sheets("Calculate").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Edit").Select

My_Range.AutoFilter Field:=2, Criteria1:="<" & Range("TeamData").Value

Rows("2:500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Calculate").Select
Range("A2").Select
ActiveSheet.Paste

End Sub

I am assuming there is a better way to copy and paste the selected data to
the other sheet based on what is returned but would not know where to start.

Column A is the team names, column B are the staff names but the team total
also has the team name in this column to make it easy to filter.

I have to filter the team total to the top of the page and then copy the
staff data below to make the rest of the spreadsheet work (not in this macro).

Any help would be appreciated