Sorting rows to a another sheet

[Dataminer]

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

[Barb Reinhardt]

Did you try using AutoFilter?
"Dataminer" <[email protected]> wrote in message
news:[email protected]...
>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

[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, 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" <[email protected]> wrote in message
news:[email protected]...
> 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

[Max]

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

[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

[Dataminer]

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