Sorting rows to a another sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

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 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
 
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)
 
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
 
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
 
Back
Top