How to cut/paste rows from one Excel sheet to 1st blank row of another sheet

Question

I need any row in Sheet1 that has the word 'Done' in column E to be cut and pasted into the next empty row of Sheet2. My problem is that I don't know how to find the next empty row in Sheet2 and start pasting values to that next empty row.

It's a basic Workbook with two Sheets. Sheet1 has all employee tasks and Sheet2 only displays the tasks that are 'Done'. Anytime an employee finishes a task and types 'Done' in a cell within Column E of Sheet1, that entire row needs to be cut and pasted into the next empty row of Sheet2. I created an ActiveX Command button in Sheet1 with the following code. The code finds any cell in Sheet1 that says 'Done' and cuts/pastes it in Sheet2, but it always starts pasting from the beginning of Sheet2, and I need it to start pasting from the next empty row of Sheet2.

Private Sub CommandButton1_Click()
Dim c As Range
Dim j As Integer
Dim Source As Worksheet
Dim Target As Worksheet

' Change worksheet designations as needed
Set Source = ActiveWorkbook.Worksheets("Sheet1")
Set Target = ActiveWorkbook.Worksheets("Sheet2")

j = 1     ' Start copying to row 1 in target sheet
For Each c In Source.Range("E1:E1000")   ' Do 1000 rows
    If c = "Done" Then
       Source.Rows(c.Row).Cut Target.Rows(j)
       j = j + 1
    End If
Next c
End Sub

My problem is that it keeps overwriting anything that's already in Sheet2 because I don't know what to replace that 'j' variable code with. I need it to find the next empty row in Sheet2 and start pasting from there.

you can use Workhseet_Change event to trigger the cut/paste automatically. Lookup how to find last used row in Excel vba. You'll get plenty of results that can help. — Scott Holtzman
yes, but my problem is that i need it to paste into the next EMPTY ROW of Sheet2. that's the main problem I'm having and which I need help with, because I can't have the script erase any of the previously completed tasks in Sheet2....it needs to add onto Sheet2, not overwrite anything. — Gjuelz
i repeat - Lookup how to find last used row in Excel vba. You'll get plenty of results that can help - this type of question has been asked many many times on SO. — Scott Holtzman
You are saying that you have "Done" in column G, however checking column E... which is it ? — FAB
Range.End, Range.Offset, Worksheet.UsedRange and For loops are all different possibilities on how to go about this — Chronocidal

FAB FAB · Accepted Answer · 2019-05-23T14:52:20

Give this a go:

Private Sub CommandButton1_Click()
Dim Source As Worksheet
Dim Target As Worksheet

Dim lRowSrc As Long, lRowDst As Long, lColSrc As Long, R As Long

' Change worksheet designations as needed
Set Source = ActiveWorkbook.Worksheets("Sheet1")
Set Target = ActiveWorkbook.Worksheets("Sheet2")

With Source
    lRowSrc = .Cells(.Rows.Count, 1).End(xlUp).row
    lColSrc = .Cells(1, .Columns.Count).End(xlToLeft).Column

    For R = lRowSrc To 1 Step -1
        If .Cells(R, 5) = "Done" Then
            lRowDst = Target.Cells(Target.Rows.Count, 1).End(xlUp).row

            Target.Range(Target.Cells(lRowDst + 1, 1), Target.Cells(lRowDst + 1, lColSrc)).Value = .Range(.Cells(R, 1), .Cells(R, lColSrc)).Value
            .Cells(R, 5).EntireRow.Delete   'get rid of the row
        End If
    Next R
End With

End Sub

Keep in mind that with a large data set, this aproach is not ideal, and using arrays is much much better. If however you only have a small number of rows (assume so...), it should be fine.

EDIT: used last row variable instead of the current row for copying values. Fixed.

How to cut/paste rows from one Excel sheet to 1st blank row of another sheet

1 Answers