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.

ni****h 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

 

[AMSVCS44]

I have a similar request... Can this be modified so that instead of cutting the row and moving to another sheet, the row is copied to another sheet? Also, instead of "yes" being the criteria I want to copy the row if the value in column K is >0.

Thanks for any help.