Sorting rows to a another sheet

[Guest]

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 A11) 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

 

[Barb Reinhardt]

Did you try using AutoFilter?

 

[davidm]

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

 

[Max]

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

Assume source data in Sheet1, A15

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)

 

[Max]

Here's a sample construct:
http://cjoint.com/?mhmtgu0vKs
AutoSortingRowsToAnotherSheet_dataminer_wks.xls

 

[davidm]

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

 

[Guest]

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