Cut from one sheet, paste in another

[Greg Snidow]

Greetings folks. I need to cut the active row from one sheet, and paste it
into the first blank row in another sheet. So far this is what I have, and I
am not sure how to do it. I can activate the target row, but I am unable to
paste into it. Any ideas? Thank you.

Dim WS As Worksheet
Dim LastRow As Integer

'Cut the active row from the present 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
'This is where I am getting errors.
.Cells(LastRow, 1).Paste
End With
'WS.Activate

Greg

 

[OssieMac]

Hi Greg,

For some reason when using Cut, Paste does not work unless you select the
worksheet then the cell and use ActiveSheet.Paste. Try the following.

..Cells(LastRow, 1).EntireRow.Insert Shift:=xlDown

Also the method you use to find the LastRow. Is it because you are looking
for the first available row and there is other data below it or is it the row
below all other data? If the former then leave as is but if the latter, the
following finds the next blank row on a worksheet.

With WS
LastRow = .Cells(.Rows.Count, 4).End(xlUp).Offset(1, 0).Row

 

[Greg Snidow]

That's a good tip about the paste not working with cut unless the destination
is not selected. The user has some validation lists in the columns starting
at row 300, so I need to find the first blank row after row 8, but before row
300, thus the method of finding the blank row.

 

[OssieMac]

Hi again Greg,

Because you say that there is other data from row 300 then the method I gave
you will continually push that data down. Therefore you need to delete a row
each time you insert one.

Note you can still use a similar method to the one I posted for finding the
next blank row by replacing '.Rows.Count' with 300.

'Find next blank row
LastRow = .Cells(300, 4).End(xlUp).Offset(1, 0).Row

'Inserts data but pushes all other data down.
..Cells(LastRow, 1).EntireRow.Insert Shift:=xlDown

'Deletes the row below inserted data
..Cells(300, 4).End(xlUp).Offset(1, 0).EntireRow.Delete