Sub CopyAMA()
Dim rng As Range
Dim rng1 As Range, rng2 As Range
With Sheets("Compacted")
.Columns("E:E").AutoFilter Field:=1, Criteria1:="AMA"
Set rng2 = ActiveSheet.AutoFilter.Range
Set rng = rng2.Columns(1).Cells
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
On Error Resume Next
Set rng1 = rng.SpecialCells(xlVisible)
On Error GoTo 0
If Not rng1 Is Nothing Then
.Cells(rng1(1).Row, "A").Resize(1, 7).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
...
I am using an old code that I have from a project several years old and am
trying to make it work for something else. In column E you have set values
(11 of them) one of which is "AMA". With this formula everything works. It
takes that column and looks for the value "AMA" and then takes that data and
copies it onto a new page. The only problem is that if there is no value of
"AMA" in the column then it copies the first row, whatever it is. I can't
have it do that, and I have no idea how to fix this, or why is is doing that
to begin with. Any help would be greatly appreciated.

Here is the code:

Sub CompactedToSeperatedData()

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

End Sub