Cut from one sheet, paste in another

[Greg Snidow]

Greeings. I apologize if this is a double post, but my first attempt does
not seem to have shown up. I need to cut the active row from the active
sheet, and paste it into the first blank row on another sheet. So far, this
is what I have. I could do it by selecting the target sheet and row, but I
would like to avoid moving from the active sheet if possible. Thank you.

Sub Test()

Dim WS As Worksheet
Dim LastRow As Integer

'Cut the active row from the active sheet
ActiveCell.EntireRow.Cut

'Set the target sheet
Set WS = ActiveWorkbook.Sheets!held

'Set start row of the destination sheet
LastRow = 8

With WS
'Get the first blank row on destination sheet
For i = 8 To 300 Step 1
If Len(.Cells(i, 4).Value & "") > 0 Then
LastRow = LastRow + 1
End If
Next i
'Need to paste the row that was cut from the previous sheet
.Range("A" & LastRow).Paste
End With

End Sub

 

[Dave Peterson]

Option Explicit
Sub Test()

Dim ToWks As Worksheet
Dim NextRow As Long

'Set the target sheet
Set ToWks = ActiveWorkbook.Worksheets("held")

With ToWks
NextRow = .Cells(.Rows.Count, "D").End(xlUp).Row + 1
If NextRow < 8 Then
NextRow = 8
End If
'Cut the active row from the active sheet
ActiveCell.EntireRow.Cut _
Destination:=.Cells(NextRow, "A")
End With

End Sub

 

[Greg Snidow]

Thanks Dave. I actually got it to work right after my post finally showed up
with this line, but your answere does exactly what I needed.

ActiveCell.EntireRow.Copy Destination:=Ws.Rows(LastRow)