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").AutoFilte
End Wit

End Su

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

I could definately be proved wrong here, but with an auto
filter row 1 is always left there regardelss of what is in
it. So you should test row 1 independently of the
autofilter

Jase
-----Original 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


.

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 luc

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").AutoFilte
End Wit

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 hel
James

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

Thanks, it did the trick perfectly