Macro to copy text into autofilter criteria

Hi everyone, I'm trying to figure out how I can set up the following.

The end desire is to have something where my end user can type in the # they want details about and than click the shiny red button and BAM the macro sends them to the details page already filtered to the number they typed in before. When I recorded the macro however the paste into the criteria search box keeps recording as the text not the actual paste of the copied text from sheet 1. the macro comes up as follows.

Range("J7").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _
"47412"

So essentially I'm just hoping to change the "47412" to .Paste (tried but just shot back an error to me.)

Thanks in advance for any help.

~James H. Cameron

Try the following.

Sub CopypasteFilter()
'
' CopypasteFilter Macro
'

'
Dim filter_value As String

filter_value = Range("J7").Value
Range("J7").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _
filter_value

End Sub

---------------------------------------------------------------------------------------------------------------------------------------------
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below

Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.