Move rows
Posted by R.Brayton on July 08, 2001 3:29 PM
I have 6 colomns of data with the headings A thru F. Each row of 6 runs through a formula and returns either 0 or 1 in column H for each row. If H1 retuns a 1 I want to move row 1 to another sheet. Say sheet 3. Make sense?
Thanks for your time.
Posted by Ivan F Moala on July 08, 2001 4:20 PM
One way to do this is through the sheets
calculation event i.e when a calculation
is in progress then this routine will run.
Don't know if suitable for your needs ???
Plus you don't say weather you want to move
the cells up??
any way .........
Private Sub Worksheet_Calculate()
Dim Lr As Double
Dim TriggerRg As Range
Dim Tcell As Range
Set TriggerRg = ActiveSheet.Range(Range("H2"), Range("H2").End(xlDown))
Application.EnableEvents = False
For Each Tcell In TriggerRg
If Tcell.Value = 1 Then
Lr = Sheets("Sheet2").Range("A65536").End(xlUp).Row
Range(Cells(Tcell.Row, 1), Cells(Tcell.Row, 6)).Cut _
Destination:=Sheets("Sheet2").Cells(Lr + 1, 1)
End If
Next
Application.EnableEvents = True
End Sub
HTH
Ivan
NB: assumes sheet2 is where you want to move it to
