You can put this in a loop or a for next statement if you know the row count

If Range("x1").Value And Range("ac1").Value And Range("ad1").Value < ""
Then
ActiveCell.EntireRow.Copy
sheets("sheetname").select
activesheet.paste
---go back to original sheet
elseIf Range("aa1").Value And Range("ab1").Value < "" Then
ActiveCell.EntireRow.Copy
sheets("sheetname").select
activesheet.paste
elseIf Range("x1").Value And Range("ac1").Value and Range("ad1").Value And
Range("ab1").Value < "" Then
ActiveCell.EntireRow.Copy
sheets("sheetname").select
activesheet.paste
sheets("sheetname").select
activesheet.paste
end if




"Dagonini" wrote:

I'm trying to make a macro that will look at various columns then copy
a row to a new spreadsheet based on the criteria.

I need to look at column X, AA, AB, AC, AD

If there is a value in X, AC, AD the row needs to copy to spreadsheet1
If there is a value in AA, AB the row needs to copy to spreadsheet2

Some rows might have a value in all fields so would need to be copied
to both spreadsheets.

I keep trying to do a simple if, then but I'm stymied on how to get it
to look at multiple criteria. For example if a value is filled in for
all the fields I don't want it to copy to a new spreadsheet multiple
times, I just want it to copy once.

I started going in this direction:

Dim eelife As Integer

eelife = Range("x1").Value

Select Case eelife

Case Is 0

but that is where I would get stuck because I don't want it to just
look at column X.

Does any one have any suggestions?