I have sheet 1 in a workbook with columns A thru D, rows 1 thru 5.
When column D in a row contains a value equal to or less than .77, I
need that entire row (as A1:D1) to be copied to sheet 2.
It would be great, but not required, if they were ordered by smallest value
in D first, but I can't have any blank rows.
I can do this very easily in Access with a query, but I need it in Excel.
Can anyone help?
Thanks
Dataminer

Did you try using AutoFilter?
"Dataminer" wrote in message
...
I have sheet 1 in a workbook with columns A thru D, rows 1 thru 5.
When column D in a row contains a value equal to or less than .77, I
need that entire row (as A1:D1) to be copied to sheet 2.
It would be great, but not required, if they were ordered by smallest
value
in D first, but I can't have any blank rows.
I can do this very easily in Access with a query, but I need it in Excel.
Can anyone help?
Thanks
Dataminer

Using Worksheet event:

Private Sub Worksheet_Change(ByVal c As Excel.Range)
If Not Intersect(c, Columns("d")) Is Nothing Then
If c.Value <= 0.77 Then
Set SrcRng = Range(Cells(c.Row, 1), Cells(c.Row, 3))
Set DesRng=Sheets("Sheet2").Range("a" &
Sheets("Sheet2").[a65536].End(xlUp).Row + 1)
SrcRng .Copy DesRng
End If
End If
End Sub

davidm


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=491059

Another option to try,
which could deliver exactly what is wanted

Assume source data in Sheet1, A1:D5

Put in E1, copy down to E5:
=IF(D1<=0.77,D1+ROW()/10^10,"")

In Sheet2,

Put in A1, copy across to D1, fill down to D5:

=IF(ISERROR(SMALL(Sheet1!$E:$E,ROWS($A$1:A1))),"", INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$E:$E,ROWS($A$1:A1)),Sheet1!$E:$E,0)))

Sheet2 auto-returns only those lines with values <=0.77 in col D in Sheet1,
sorted in ascending order, all neatly bunched at the top (no intervening
blank rows)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Dataminer" wrote in message
...
I have sheet 1 in a workbook with columns A thru D, rows 1 thru 5.
When column D in a row contains a value equal to or less than .77, I
need that entire row (as A1:D1) to be copied to sheet 2.
It would be great, but not required, if they were ordered by smallest
value
in D first, but I can't have any blank rows.
I can do this very easily in Access with a query, but I need it in Excel.
Can anyone help?
Thanks
Dataminer

Here's a sample construct:
http://cjoint.com/?mhmtgu0vKs
AutoSortingRowsToAnotherSheet_dataminer_wks.xls
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

For a VBA solution:

Sub ConditionalCopy()
Dim rng As Range
For Each c In Range("d:d")
If Not IsEmpty(c) Then
If c.Value <= 0.77 Then
If rng Is Nothing Then
Set rng = Range(Cells(c.Row, 1), Cells(c.Row, 4))
Else
Set rng = Union(rng, Range(Cells(c.Row, 1), Cells(c.Row, 4)))
End If
End If
End If
Next
If Not rng Is Nothing Then
rng.Copy Sheets("Sheet2").Range("a1")
End If
End Sub

Davidm


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=491059

My thanks to everyone for their
valuable time on this.
I'm eager to get back to my desk
to try all ideas out. I'll try and
post results by weekend.
Dataminer