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
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
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
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.
We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel
Which adblocker are you using?
Disable AdBlock
Follow these easy steps to disable AdBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the icon in the browser’s toolbar. 2)Click on the "Pause on this site" option.
Go back
Disable AdBlock Plus
Follow these easy steps to disable AdBlock Plus
1)Click on the icon in the browser’s toolbar. 2)Click on the toggle to disable it for "mrexcel.com".
Go back
Disable uBlock Origin
Follow these easy steps to disable uBlock Origin
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.
Go back
Disable uBlock
Follow these easy steps to disable uBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.