Cut rows if contains "Done" from sheet1, and paste onto next available row in sheet2 in the same workbook.
 

Hi, all.

I faced VBA issue. IF you can spare me some time to do this project, it
would be super nice.
On sheet1, I will select entire row by clicking row number on the left,
and I wisg I have a macro to send this selected row to next available
row in sheet2.
I used something like this, but doesn't help.

Private Sub to1()

Selection.Cut
Sheets("1").Range("b65000").End(xlUp).Offset(1, 0).Value =
Sheets("Prioritization").Selection

End Sub

and I also used this.

Sub CutData()

Dim sStr As String, Cell As Range, rng As Range
sStr = "#Done#"
For Each Cell In Range("n7:n900")
If InStr(1, sStr, Cell.Value, vbTextCompare) 0 Then
If rng Is Nothing Then
Set rng = Cell
Else
Set rng = Union(rng, Cell)
End If
End If
Next
If Not rng Is Nothing Then
rng.EntireRow.Copy Destination:=Worksheets( _
"OtherSheet").Range("A1")
rng.EntireRow.Delete
End If

End Sub



But you know what? If I can have a macro actually sends rows contains
"done" from sheet1 to sheet2. it would be super nice. Once the row has
"done" in it, "done" will be located in "N" column.

Thank you again.

Cut rows if contains "Done" from sheet1, and paste onto next available row in sheet2 in the same workbook.
 

here's a very basic concept.

it will copy any row that has done in column n from 7 to 900
then copy it to sheet2 starting in row 4
then delete the row on sheet1

adapt to your needs


Sub move_rows()


Dim i As Integer
i = 4
Application.ScreenUpdating = False

With Worksheets("sheet1")
For Each cell In Range("N7:n900")
If UCase(cell.Value) = "DONE" Then
cell.EntireRow.Copy
Worksheets("sheet2").Range("a" & i).PasteSpecial
cell.EntireRow.Delete
i = i + 1
End If
Next
End With
Application.ScreenUpdating = True
End Sub

--


Gary


"Steven" wrote in message
oups.com...
Hi, all.

I faced VBA issue. IF you can spare me some time to do this project, it
would be super nice.
On sheet1, I will select entire row by clicking row number on the left,
and I wisg I have a macro to send this selected row to next available
row in sheet2.
I used something like this, but doesn't help.

Private Sub to1()

Selection.Cut
Sheets("1").Range("b65000").End(xlUp).Offset(1, 0).Value =
Sheets("Prioritization").Selection

End Sub

and I also used this.

Sub CutData()

Dim sStr As String, Cell As Range, rng As Range
sStr = "#Done#"
For Each Cell In Range("n7:n900")
If InStr(1, sStr, Cell.Value, vbTextCompare) 0 Then
If rng Is Nothing Then
Set rng = Cell
Else
Set rng = Union(rng, Cell)
End If
End If
Next
If Not rng Is Nothing Then
rng.EntireRow.Copy Destination:=Worksheets( _
"OtherSheet").Range("A1")
rng.EntireRow.Delete
End If

End Sub



But you know what? If I can have a macro actually sends rows contains
"done" from sheet1 to sheet2. it would be super nice. Once the row has
"done" in it, "done" will be located in "N" column.

Thank you again.

Cut rows if contains "Done" from sheet1, and paste onto next available row in sheet2 in the same workbook.
 

try something like this
data in 4 th row in sheet1 is copited to 7th row in sheet2

Public Sub test()
Worksheets("sheet1").Rows("4:4").Copy
Worksheets("sheet2").Rows("7:7").PasteSpecial
application.CutCopyMode=false
worksheets("sheet2").range("a7").select
End Sub

==================
"Steven" wrote in message
oups.com...
Hi, all.

I faced VBA issue. IF you can spare me some time to do this project, it
would be super nice.
On sheet1, I will select entire row by clicking row number on the left,
and I wisg I have a macro to send this selected row to next available
row in sheet2.
I used something like this, but doesn't help.

Private Sub to1()

Selection.Cut
Sheets("1").Range("b65000").End(xlUp).Offset(1, 0).Value =
Sheets("Prioritization").Selection

End Sub

and I also used this.

Sub CutData()

Dim sStr As String, Cell As Range, rng As Range
sStr = "#Done#"
For Each Cell In Range("n7:n900")
If InStr(1, sStr, Cell.Value, vbTextCompare) 0 Then
If rng Is Nothing Then
Set rng = Cell
Else
Set rng = Union(rng, Cell)
End If
End If
Next
If Not rng Is Nothing Then
rng.EntireRow.Copy Destination:=Worksheets( _
"OtherSheet").Range("A1")
rng.EntireRow.Delete
End If

End Sub



But you know what? If I can have a macro actually sends rows contains
"done" from sheet1 to sheet2. it would be super nice. Once the row has
"done" in it, "done" will be located in "N" column.

Thank you again.

Cut rows if contains "Done" from sheet1, and paste onto next available row in sheet2 in the same workbook.
 

forgot, since rows are being deleted, have to go backwards.

you can set variables if you like, but i explicitly defined everything here

Sub move_rows()
Dim i As Long, x As Long
i = 4
x = 7
Application.ScreenUpdating = False
For x = 900 To 7 Step -1
If UCase(Range("n" & x).Value) = "DONE" Then
Range("n" & x).EntireRow.Copy
Worksheets("sheet2").Range("a" & i).PasteSpecial
Range("n" & x).EntireRow.Delete
i = i + 1
End If
Next
Application.ScreenUpdating = True
Application.CutCopyMode = False
End Sub

--


Gary


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
here's a very basic concept.

it will copy any row that has done in column n from 7 to 900
then copy it to sheet2 starting in row 4
then delete the row on sheet1

adapt to your needs


Sub move_rows()


Dim i As Integer
i = 4
Application.ScreenUpdating = False

With Worksheets("sheet1")
For Each cell In Range("N7:n900")
If UCase(cell.Value) = "DONE" Then
cell.EntireRow.Copy
Worksheets("sheet2").Range("a" & i).PasteSpecial
cell.EntireRow.Delete
i = i + 1
End If
Next
End With
Application.ScreenUpdating = True
End Sub

--


Gary


"Steven" wrote in message
oups.com...
Hi, all.

I faced VBA issue. IF you can spare me some time to do this project, it
would be super nice.
On sheet1, I will select entire row by clicking row number on the left,
and I wisg I have a macro to send this selected row to next available
row in sheet2.
I used something like this, but doesn't help.

Private Sub to1()

Selection.Cut
Sheets("1").Range("b65000").End(xlUp).Offset(1, 0).Value =
Sheets("Prioritization").Selection

End Sub

and I also used this.

Sub CutData()

Dim sStr As String, Cell As Range, rng As Range
sStr = "#Done#"
For Each Cell In Range("n7:n900")
If InStr(1, sStr, Cell.Value, vbTextCompare) 0 Then
If rng Is Nothing Then
Set rng = Cell
Else
Set rng = Union(rng, Cell)
End If
End If
Next
If Not rng Is Nothing Then
rng.EntireRow.Copy Destination:=Worksheets( _
"OtherSheet").Range("A1")
rng.EntireRow.Delete
End If

End Sub



But you know what? If I can have a macro actually sends rows contains
"done" from sheet1 to sheet2. it would be super nice. Once the row has
"done" in it, "done" will be located in "N" column.

Thank you again.