If the data starts in row1

Sub CopyAMA()
Dim rng1 As Range, rng2 As Range, rng3 As Range
With Sheets("Compacted")
.Columns("E:E").AutoFilter Field:=1, Criteria1:="AMA"
Set rng2 = .AutoFilter.Range
Set rng1 = rng2.Columns(1).SpecialCells(xlVisible)
If rng1.Count 1 Then
Set rng3 = Intersect(rng2.EntireRow, .Columns("A:G"))
rng3.Copy _
Destination:=Sheets("SeperatedData").Range("A3")
Else
MsgBox "No visible rows"
End If
rng2.AutoFilter
End With
End Sub

--
Regards,
Tom Ogilvy

James Stephens wrote in message
...
Thanks for the quick response. I tried it out and it seems to work, only
one problem and it is my fault I think. In the column that is searches for
"AMA" - column E - there may be more than one instance of "AMA" in that
column - there are 11 different possibilities but up to a couple hundred
entries - the code you provided me works to eliminate the first problem but
now it only returns one row no matter how many occurances there are of the
test value - any help would be greatly appreciated - I tried putting some of
my old code into the area where I thought it might work in this new one -
but no luck

I put my origional code:

With Sheets("Compacted")
.Columns("E:E").AutoFilter Field:=1, Criteria1:="AMA"
.Columns("A:G").SpecialCells(xlCellTypeVisible).Co py
Sheets("SeperatedData").Range("A3")
.Columns("G:G").AutoFilter
End With

In place of this section of Tom's Code, no luck though:

.Cells(rng1(1).Row, "A").Resize(1, 7).Copy _
Destination:=Sheets("SeperatedData").Range("A3")

Thanks in advance for any help
James