Move row to another sheet

[A.B.]

Hi,
I need some help on below:
If the cell entry is "yes", then the whole row should be moved (cut) to an empty row in another sheet (e.g. sheet2).
If possible, using built-in formulae

Many thanks

[Dave Hawley]

Hi A.B

Not with a function no! Would you like some VBA code?

[nisht]

Sub cuttosheet()
Dim ir As Integer
Dim i As Integer
ir = [a1].CurrentRegion.Rows
For i = 1 To ir
sheet1.select
If Range("a:" & i).Value = "yes" Then
Range("a:" & i).EntireRow.Cut
Sheet2.Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.PasteSpecial xlPasteAll
End If
Next i
End Sub

This code should work..
I have not checked the code..if you find difficulty then write back to me.

nishith desai
http://www.pexcel.com

[ This Message was edited by: nisht on 2002-03-27 02:21 ]

[A.B.]

Thank you all
It's OK with VB code (since no other way), but can you make it as a user-defined funtion?

Nisht,
The code returns run-time error 13 "type mismatch" line 4

Thanks again

[Adcb]

On 2002-03-27 09:07, A.B. wrote:
Thank you all
It's OK with VB code (since no other way), but can you make it as a user-defined funtion?

Nisht,

The code returns run-time error 13 "type mismatch" line 4

Thanks again

Functions cannot change the Excel environment, they can only return results.

Try this macro :-

Sub cuttosheet()
Dim sRng As Range, cell As Range
Dim dRng As Range
Set sRng = Sheets("Sheet1").Range([A1], [A65536].End(xlUp))
For Each cell In sRng
If cell.Value = "yes" Then
Set dRng = Sheets("Sheet2").[A65536].End(xlUp)(2, 1)
cell.EntireRow.Cut dRng
End If
Next
End Sub

[A.B.]

Excellent
Two more things:
1) If the column that contains "yes/no" is in e.g. D
2) Delete the entire "copied" rows in sheet1

what would be the changes?

cheers