This may be what you are looking for:
Sub CopyCA()

Dim DestSheet As Worksheet
Set DestSheet = Worksheets("Sheet2")

Dim sRow As Long
Dim dRow As Long
Dim sCount As Long
sCount = 0
dRow = 0

myword = InputBox("Enter items to search for.")

For sRow = 1 To Range("A65536").End(xlUp).Row

If Cells(sRow, "A") Like "*" & myword & "*" Then
sCount = sCount + 1
dRow = dRow + 1
Cells(sRow, "A").Copy Destination:=DestSheet.Cells(dRow, "A")
End If
Next sRow

MsgBox sCount & " Significant rows copied", vbInformation, "Transfer Done"

End Sub

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Nelson" wrote:

I've seen previous posts on this, but most are over 2 years old. I'm
operating 2007 but routinely save in 2003 for other users. If this is a 2007
solution only, that works for me:

Is it possible to filter a range using either a partial or exact match from
a remote cell? Value retained in cell A1 ( that can change) with the
filtered area being A10:Z100 ( or something like that).

It is not very productive to constantly use the drop downs and search or use
the "contains" function within the filter.

Any help would be greatly appreciated !!