excel - VBA - Copy and Paste Table Row to Another Table

Question

I am very new to VBA and I am trying to solve what I think should be a very simple problem (I have a Java/J2EE background)... I am looping through a table and want to copy rows to a table on another worksheet based on some conditional statements. See code below.

Sub closeActionItems()
    Dim i, iLastRow As Integer
    Dim date1 As Date
    Dim oLastRow As ListRow

    date1 = Date
    iLastRow = ActiveSheet.ListObjects("Open_Items").ListRows.Count

    For i = 6 To iLastRow
        If Cells(i, 7).Value <= date1 And Cells(i, 7).Value <> vbNullString Then
            Rows(i).Copy
            Set oLastRow = Worksheets("Closed").ListObject("Closed_Items").ListRows.Add
            'Paste into new row

            Application.CutCopyMode = False
            Rows(i).EntireRow.Delete
        End If
    Next
End Sub

I have tried many different iterations but have been unable to find the correct way to copy the contents of the clipboard to the newly created row.

Any help would be appreciated. Thanks ahead of time.

Answer 1

Define srcRow as a Range like so:

Dim srcRow as Range

Then in your loop try doing this:

        Set srcRow = ActiveSheet.ListObjects("Open_Items").ListRows(i).Range
        Set oLastRow = Worksheets("Closed").ListObjects("Closed_Items").ListRows.Add

        srcRow.Copy
        oLastRow.Range.PasteSpecial xlPasteValues

        Application.CutCopyMode = False
        Rows(i).EntireRow.Delete

Notice that you still have a problem in that you are deleting rows as you are trying to loop through them, so you probably want to change your loop so that it starts at the bottom and goes up.