Hi Mike,

Many thanks

Excuse my ignorance, but what is worksheet code and how and where do I enter
it. Do I put it in a cell?

John

"Mike H" wrote:

Hi,

This is worksheet code and goes in the sheet that contains the source data.
Change DestSheet to the name of the sheet you want the data pasting

Sub Stance()
Dim MyRange
Dim copyrange As Range
DestSheet = "Sheet3"
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A2:A" & lastrow)
For Each c In MyRange
If UCase(c.Value) = "JD" And UCase(c.Offset(, 1).Value) = "ELECTIVE" Then
If copyrange Is Nothing Then
Set copyrange = c.Resize(, 4)
Else
Set copyrange = Union(copyrange, c.Resize(, 4))
End If
End If
Next
If Not copyrange Is Nothing Then
copyrange.Copy Destination:=Sheets(DestSheet).Range("A1")
End If
End Sub

Mike

"JRD" wrote:

Is there a function in excel 2007 and 2003 for picking only certain rows /
cells from a worksheet and copying them to a seperate worksheet in the same
document

e.g.

A B C D
NAME PRIORITY ALIVE? LAB NO

1 JD ELECTIVE Y 1
2 AN ELECTIVE N 2
3 ST URGENT Y 3
4 AN URGENT Y 1
5 JD URGENT N 2
6 JD ELECTIVE Y 3
7 ST ELECTIVE N 2

How can I pick out all the rows where column A (Name) is JD and column B
(priority) is elective and then copy the data in columns A, B and C only in
these rows only to another worksheet in the same document.

Therefore on a seperate work sheet the following would appear for the
example above:

A B C
NAME PRIORITY ALIVE?

1 JD ELECTIVE Y
2 JD ELECTIVE Y
3 ST URGENT Y

Can excel 2003 or maybe 2007 do this?

Thanks

John