VBA to autofilter another worksheet with a copy done on the previous

[osf9]

Sorry for the long title, but just wanted to try to explain my problem.

I have 2 worksheets: "Results" and "Trend". I need a macro in order to:

- select a cell in "Results"
- copy the value of the cell
- filter the first column of "Trend" of the value copied

At the beginning of the process, of course, I want the clear the filter previously done on "Trend".

I tried by my own and this is what I get:

Sub Macro6()
'
' Macro6 Macro
'

'
    Sheets("Trend").Select
    Application.CutCopyMode = False
    ActiveSheet.ShowAllData
    Sheets("Results").Select
    Selection.Copy
    Sheets("Trend").Select
    ActiveSheet.Range("$A$9:$AB$4149").AutoFilter Field:=1, Criteria1:= _
        "9940088806"
End Sub

That's all working good, apart the selection of the filter, where, instead of having a fix value, I need the have a sort of "paste command".

Do you think it's feasible?

Thanks, regards, Carlo

 

[Andrew Poulsom]

Maybe try:

Sub Macro6()
'
' Macro6 Macro
'
'
    Dim Crit As Variant
    Sheets("Trend").Select
    Application.CutCopyMode = False
    ActiveSheet.ShowAllData
    Sheets("Results").Select
    Crit = Selection.Value
    Sheets("Trend").Select
    ActiveSheet.Range("$A$9:$AB$4149").AutoFilter Field:=1, Criteria1:=Crit
End Sub

 

[osf9]

Andrew... Faster than light, but most important... PERFECT!

Thanks for your help, appreciated.