Button click, add line of data to new row at the bottom of a table

[BuffaloGuy]

I am finding different VBA code they is trying to do the same thing, and I can't get anything to work.

I have data from cell A2:BN2 on Sheet "Initial"
I have a Table68914 on sheet "History", when the data is ready, I want to click button "Transfer" and copy data A2:BN2 and add it to a new row on Table 68914

 

[BuffaloGuy]

Sub History_Click()


Dim lrow As Long
lrow = Sheets("Initial").Cells(Rows.Count, 3).End(xlUp).Row
With Sheets("Initial")
    .Range("a2:bn2").Copy
    
    Sheets("History").Range("a" & lrow + 1).PasteSpecial xlPasteValues
End With


End Sub

This is what I have so far. It copies the data in the first sheet, and pastes it onto row 41 of sheet History. It doesn't add it to the table on the history sheet

 

[Logit]

Let me know if this works. Untested here.

Sub History_Click()
    Dim wsInitial As Worksheet
    Dim wsHistory As Worksheet
    Dim tbl As ListObject
    Dim newRow As ListRow

    ' Set references to sheets and table
    Set wsInitial = ThisWorkbook.Sheets("Initial")
    Set wsHistory = ThisWorkbook.Sheets("History")
    Set tbl = wsHistory.ListObjects("Table68914")   '<--- change table name here

    ' Add a new row to the table
    Set newRow = tbl.ListRows.Add

    ' Copy data from Initial sheet to the new row in the table
    wsInitial.Range("A2:BN2").Copy
    newRow.Range.PasteSpecial Paste:=xlPasteValues

    ' Clear the clipboard
    Application.CutCopyMode = False
End Sub

 

[Alex Blakenburg]

If the tables do not have the same number of columns you might need to change the PasteSpecial line to this:

    newrow.Range.Cells(1).PasteSpecial Paste:=xlPasteValues

 

[BuffaloGuy]

Let me know if this works. Untested here.

Sub History_Click()
    Dim wsInitial As Worksheet
    Dim wsHistory As Worksheet
    Dim tbl As ListObject
    Dim newRow As ListRow

    ' Set references to sheets and table
    Set wsInitial = ThisWorkbook.Sheets("Initial")
    Set wsHistory = ThisWorkbook.Sheets("History")
    Set tbl = wsHistory.ListObjects("Table68914")   '<--- change table name here

    ' Add a new row to the table
    Set newRow = tbl.ListRows.Add

    ' Copy data from Initial sheet to the new row in the table
    wsInitial.Range("A2:BN2").Copy
    newRow.Range.PasteSpecial Paste:=xlPasteValues

    ' Clear the clipboard
    Application.CutCopyMode = False
End Sub

Thats the ticket! Thank you!

 

[Logit]

You are welcome.

 

[BuffaloGuy]

You are welcome.

is there a way to modify this code to overwrite a line based on a reference number in column B?

The click to add data to a table works perfect, but I reference it in a different sheet where it is available for review and editing. Could I have a second button that copies row A2:BN2 in worksheet "Review" and pastes over the row it references based on the value in column B?

I dont know if that is clear.

 

[Logit]

It would be more easily understood if you could post your workbook (not confidential information) to a download site and post the link here.
In your workbook make notations what you are wanting to accomplish. Thanks.