Hi,

I have tried to make some code that will search through a file and
move rows based on certain criteria to new spreadsheets. Code as
follows:

Sub life_file()

If Range("x1").Value Or Range("ae1").Value Or Range("ah1").Value 0
Then
ActiveCell.EntireRow.Copy
Sheets("LIFE").Select
ActiveSheet.Paste
Sheets("EOI_TEST").Select
ElseIf Range("aa1").Value Or Range("ac1").Value 0 Then
ActiveCell.EntireRow.Copy
Sheets("LTD_STD").Select
ActiveSheet.Paste
ElseIf Range("x1").Value And Range("ae1").Value And Range("ah1").Value
And _
Range("aa1").Value And Range("ac1").Value 0 Then
ActiveCell.EntireRow.Copy
Sheets("LIFE").Select
ActiveSheet.Paste
Sheets("LTD_STD").Select
ActiveSheet.Paste
End If


End Sub

The problem is that I can only make it look at the first row on my
file. There could be a varying number of rows on the file so i can't
just sent a specific range for the macro to look at. I'm not sure how
to make it loop through until all rows have been looked at. Can some
one help?

Thanks!

Is this what you want?

Sub life_file()
Dim LastRow As Long
Dim i As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "X").End(xlUp).Row
For i = 1 To LastRow

If .Cells(i, "X").Value 0 Or _
.Cells(i, "AE").Value 0 Or _
.Cells(i, "AH").Value 0 Then
.Rows(i).Copy Sheets("LIFE").Range("A1")

ElseIf .Cells(i, "AA").Value 0 Or _
.Cells(i, "AC").Value 0 Then
.Rows(i).Copy Sheets("LTD_STD").Range("A1")

ElseIf .Cells(i, "X").Value 0 And _
.Cells(i, "AE").Value 0 And _
.Cells(i, "AH").Value 0 And _
.Cells(i, "AA1").Value 0 And _
.Cells(i, "AC").Value 0 Then
.Rows(i).Copy Sheets("LIFE").Range("A1")
.Rows(i).Copy Sheets("LTD_STD").Range("A1")
End If
Next i
End With

End Sub



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Dagonini" wrote in message
...
Hi,

I have tried to make some code that will search through a file and
move rows based on certain criteria to new spreadsheets. Code as
follows:

Sub life_file()

If Range("x1").Value Or Range("ae1").Value Or Range("ah1").Value 0
Then
ActiveCell.EntireRow.Copy
Sheets("LIFE").Select
ActiveSheet.Paste
Sheets("EOI_TEST").Select
ElseIf Range("aa1").Value Or Range("ac1").Value 0 Then
ActiveCell.EntireRow.Copy
Sheets("LTD_STD").Select
ActiveSheet.Paste
ElseIf Range("x1").Value And Range("ae1").Value And Range("ah1").Value
And _
Range("aa1").Value And Range("ac1").Value 0 Then
ActiveCell.EntireRow.Copy
Sheets("LIFE").Select
ActiveSheet.Paste
Sheets("LTD_STD").Select
ActiveSheet.Paste
End If


End Sub

The problem is that I can only make it look at the first row on my
file. There could be a varying number of rows on the file so i can't
just sent a specific range for the macro to look at. I'm not sure how
to make it loop through until all rows have been looked at. Can some
one help?

Thanks!

What is the name of your source sheet("EOI_TEST")?
Where on the destination sheet(s) do you want the data to go
You do NOT need to select
You DO need to qualify each in your OR
If Range("x1").Value0 Or Range("ae1").Value0 Or Range("ah1").Value 0


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dagonini" wrote in message
...
Hi,

I have tried to make some code that will search through a file and
move rows based on certain criteria to new spreadsheets. Code as
follows:

Sub life_file()

If Range("x1").Value Or Range("ae1").Value Or Range("ah1").Value 0
Then
ActiveCell.EntireRow.Copy
Sheets("LIFE").Select
ActiveSheet.Paste
Sheets("EOI_TEST").Select
ElseIf Range("aa1").Value Or Range("ac1").Value 0 Then
ActiveCell.EntireRow.Copy
Sheets("LTD_STD").Select
ActiveSheet.Paste
ElseIf Range("x1").Value And Range("ae1").Value And Range("ah1").Value
And _
Range("aa1").Value And Range("ac1").Value 0 Then
ActiveCell.EntireRow.Copy
Sheets("LIFE").Select
ActiveSheet.Paste
Sheets("LTD_STD").Select
ActiveSheet.Paste
End If


End Sub

The problem is that I can only make it look at the first row on my
file. There could be a varying number of rows on the file so i can't
just sent a specific range for the macro to look at. I'm not sure how
to make it loop through until all rows have been looked at. Can some
one help?

Thanks!

I'm not sure I understand what you mean...do I need to qualifiy the
range? Gah, I'm such a noob at this and am trying to pick it up on
the fly. I put in the 0 on each range.

But it is only moving the last row on the file and is not moving the
other rows.

Looks like Bob gave you a good answer to work with.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dagonini" wrote in message
...
I'm not sure I understand what you mean...do I need to qualifiy the
range? Gah, I'm such a noob at this and am trying to pick it up on
the fly. I put in the 0 on each range.

But it is only moving the last row on the file and is not moving the
other rows.

When I try using Bob's it still only pastes the last row on the file
into a new spreadsheet. I

If desired, send your workbook to my address below along with snippets of
these msgs, a clear explanation of what you want and examples of
before/after

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dagonini" wrote in message
...
When I try using Bob's it still only pastes the last row on the file
into a new spreadsheet. I