VBA - Copy and Paste Table Row to Another Table
37,990
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.
Author by
user1651899
Updated on September 07, 2020Comments
-
user1651899 over 3 years
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.
-
user1651899 over 11 yearsThanks this worked perfectly! You were right about the looping issue as well and I corrected that. Thanks for the help!