0

I am using this code to copy/paste an entire row if a column contains a keyword. I assigned the macro to a button. It works, but if I get a new item that is marked complete and press the button again, it erases everything on the 'Complete' worksheet and pastes the new information. I want it to paste it under the last row.

    Dim i As long
    Dim p As long
    Dim Source As Worksheet
    Dim Target As Worksheet

    Set Source = ActiveWorkbook.Worksheets("In Progress")
    Set Target = ActiveWorkbook.Worksheets("Complete")

    p = 3     ' Start copying to row 3 in target sheet
    Dim lastrow As Integer: lastrow = Source.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

    For i = lastrow To 3 Step -1
        If Source.Range("B" & i).Value = "Complete" Then
        Source.Rows(i).Copy Target.Rows(p)
        Source.Rows(i).EntireRow.Delete
        p = p + 1
    End If
Next
End Sub

After Googling, it appears I need something like

lastrow = Sheet2.Range("A99999").End(xlUp).Row + 1.

Am I somewhat close to what I need to solve it? Thank you for your assistance.

InvisibleInk
  • 43
  • 1
  • 8
  • 1
    Does this answer your question? [Error in finding last used cell in Excel with VBA](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) – BigBen Jan 28 '20 at 12:41
  • Don't use `Integer` either, use `Long`. – BigBen Jan 28 '20 at 12:41
  • Yes I tested it and it wasn't working for me so I was posting to know if I was even on the right track. – InvisibleInk Jan 28 '20 at 12:43
  • Thank you BigBen. I will read that and see if I can solve it. – InvisibleInk Jan 28 '20 at 12:49
  • Your problem is that you are using `p = 3` to start your paste to the `Target` sheet. So when you re-initiate the macro again, it will overwrite every row on the `Target` sheet. You will need to dimension a different last row variable for each worksheet. – GMalc Jan 28 '20 at 12:59

1 Answers1

0

Working version:

Sub TransferComplete()
    Dim i As long
    Dim p As long
    Dim Source As Worksheet
    Dim Target As Worksheet

    Set Source = ActiveWorkbook.Worksheets("In Progress")
    Set Target = ActiveWorkbook.Worksheets("Complete")

    p = Target.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1    ' Start copying to last row in target sheet
    Dim lastrow As long: lastrow = Source.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

    For i = lastrow To 3 Step -1
        If Source.Range("B" & i).Value = "Complete" Then
        Source.Rows(i).Copy Target.Rows(p)
        Source.Rows(i).EntireRow.Delete
        p = p + 1
    End If
Next
End Sub

I removed p = 3 and replaced it with p = Target.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1

InvisibleInk
  • 43
  • 1
  • 8