Paste rows into next empty row

Question

I'm trying to cut rows with status column populated with the word "Deployed" and paste them into a new worksheet called "deployed". I need to paste the rows into the next empty row so no existing rows on the "deployed" worksheet are copied over.

I started off my code reformatting the "deployed" worksheet every time the marco ran but have been told I need to keep all the data and add to it, now I'm stuck. I can't find anywhere online how to do this.

This is the code I have which I need to change to copy onto next available empty row instead of row 2. Please help!

Sub CutRows() 'cuts all rows with status deployed into the Deployed worksheet - working
    Dim cell As Range
    Dim lastRow As Long
    Dim i As Long
    Set Wksh1 = Worksheets("01 Feb 19")
    Set Wksh2 = Worksheets("Deployed")

    lastRow = Wksh1.Range("A" & Rows.Count).End(xlUp).Row 'finds the last row

    i = 2 ' copies onto wksh2 row 2

    For Each cell In Wksh1.Range("T1:T" & lastRow) 'looks in T column until the last row (loop)
        If cell.Value = "Deployed" Then 'searches for word deployed
            cell.EntireRow.Cut Wksh2.Cells(i, 1) 'cuts entire row into Deployed worksheet
            i = i + 1 'Added so that rows don't overwrite each other
        End If
    Next cell 'To close the cell loop (For loop)
End Sub

How about [this](https://stackoverflow.com/questions/38882321/excel-vba-better-way-to-find-last-row) — Tate Garringer, Feb 26 '19 at 16:14

score 0 · Accepted Answer · answered Feb 26 '19 at 16:14

0

Just replace i = 2 with i = Wksh2.Range("A" & Wksh2.Rows.Count).End(xlUp).Row + 1

answered Feb 26 '19 at 16:14

Pᴇʜ

56,719
10
49
73

1

Beautiful, that makes perfect sense now that I see it! Thank you. – Jodie Feb 26 '19 at 16:26

Paste rows into next empty row

1 Answers1