Copy and Paste to a Table

[Philip1957]

Greetings,

I am attempting to copy a row from a table on 1 worksheet and paste it into another table on a different worksheet in the same workbook.
The copy & paste works, but the pasted row is not included as part of the table on the second worksheet. What am I doing wrong?

Sub CopyNumDate()
' Copy ECO# & Due Date from Approvals to Days late
Dim lRow As Long

With Sheets("Approvals")
    lRow = .Range("A" & Rows.Count).End(xlUp).Row
    .Rows(lRow).Copy Destination:=Sheets("Days Late").Range("A" & Rows.Count).End(xlUp).Offset(1)
End With

    
End Sub   'CopyNumDate

Thanks in advance for any assistance.

~ Phil

 

[Domenic]

That's because you're copying the entire row instead of the Table's row. However, since you're working with Tables, here's another way...

Sub CopyNumDate()
  
    With ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1")
        Dim sourceRange As Range
        Set sourceRange = .ListRows(.ListRows.Count).Range
    End With
  
    With ThisWorkbook.Worksheets("Sheet2").ListObjects("Table2")
        Dim newListRow As ListRow
        Set newListRow = .ListRows.Add
    End With
  
    sourceRange.Copy
    newListRow.Range.PasteSpecial xlPasteValues
  
    Application.CutCopyMode = False
  
End Sub

Hope this helps!

 

[Philip1957]

Thank you for the assistance Domenic. Your code worked fine, but broke the next sub in my procedure. I clearly need to study up on ListObjects.
I tried using the ListObject syntax from your code to modify the broken sub (CopyFormula) but I'm not having any luck. I'm trying to copy the formula from the cells above into the new row that was inserted. The range is C : BC.

Here is my entire procedure. I realize this isn't the most efficient or elegant way to build macros, but breaking it into pieces like this helps me to learn by isolating mistakes. If you have any advice on what I've got wrong with the sub CopyFormula I would be grateful.

Option Explicit

Sub Add_To_Scoreboard()

Dim response As VbMsgBoxResult
response = MsgBox("Did you enter the ECO# & due date?", vbYesNo)
If response = vbNo Then
    MsgBox "Please do that first and try again"
    Exit Sub
End If

CopyNumDate
CopyFormula
Sortx2
GoHome

End Sub

Private Sub CopyNumDate()
' Copy ECO# & Due Date from Approvals to Days late
        
With ThisWorkbook.Worksheets("Approvals").ListObjects("tblApproved")
        Dim sourceRange As Range
        Set sourceRange = .ListRows(.ListRows.Count).Range
End With
    
With ThisWorkbook.Worksheets("Days Late").ListObjects("tblLate")
        Dim newListRow As ListRow
        Set newListRow = .ListRows.Add
End With
    
    sourceRange.Copy 'newListRow.Range
    newListRow.Range.PasteSpecial xlPasteValues
    

Sheets("Days Late").Activate
    
End Sub   'CopyNumDate


Private Sub CopyFormula()

With ThisWorkbook.Worksheets("Days Late").ListObjects("tblLate")
        Dim sourceRange As Range
        Set sourceRange = .ListRows(.ListRows.Count).Range
End With
    
With ThisWorkbook.Worksheets("Days Late").ListObjects("tblLate")
        Dim newForm As ListRow
        Set newForm = .ListRows.Offset(-1, 3)
End With
    
    sourceRange.Copy
    newForm.Range.PasteSpecial xlPasteValues


End Sub   'CopyFormula

Private Sub Sortx2()

    Dim sht1 As Worksheet, StartCell1 As Range, Selection1 As Range
    Dim sht2 As Worksheet, StartCell2 As Range, Selection2 As Range

    Set sht1 = Worksheets("Approvals")
    Set StartCell1 = sht1.Range("A1")

    Set Selection1 = StartCell1.CurrentRegion

    Selection1.Sort Key1:=StartCell1.Offset(0, 1), Order1:=xlAscending, Header:=xlYes, Orientation:=xlSortColumns

    Set sht2 = Worksheets("Days Late")
    Set StartCell2 = sht2.Range("A1")

    Set Selection2 = StartCell2.CurrentRegion

    Selection2.Sort Key1:=StartCell2.Offset(0, 1), Order1:=xlAscending, Header:=xlYes, Orientation:=xlSortColumns


End Sub   'Sortx2

Private Sub GoHome()
Dim lRow As Long
lRow = ThisWorkbook.Worksheets("Approvals").Range("A" & Rows.Count).End(xlUp).Row
    
    Sheets("Approvals").Activate
    Range("A" & lRow).Select

End Sub   'GoHome

Thanks for your time & patience,
Phil

 

[Domenic]

It's always best to point out which line is causing the error, and which error it specifies. In any case, it looks like the line causing the error is this one...

        Set newForm = .ListRows.Offset(-1, 3)

If you're adding a new row to your table, it should be...

        Set newForm = .ListRows.Add

However, you say that you're using CopyFormula() to copy the formula to the newly created row. Actually, formulas should automatically be copied to newly created rows in a Table. If this is not the case in your situation, make sure that the formulas in a column are all the same. You might need to re-enter the formula in the first row and then copy down.

 

[Philip1957]

Domenic,
I redid the formulas but it still doesn't copy down.
I think I need to go back and read up on List Objects and then try again.

In the meantime, I converted my tables back into ranges and rewrote the macro. It works fine. There wasn't any reason the data had to be in tables, I was just trying something new (for me).

Thanks for your assistance!

~ Phil