VBA: Copy/Paste Data Between Tables

[zero269]

Hello,

I've been looking at ways to automate copying data from a Table in one Sheet to another Table on another Sheet.

VBA looks like the way to go, but I'm not well versed on decoding VBA to make sense to build or fully edit to achieve my goal.

I normally add a new row at the end of the Table on Sheet2, then Copy and Paste > Values into that new row which expands automatically.

I will later purge those Copied rows from the old table, but for now, If I can automate the Copy/Paste, that would be very helpful.

Using my Sample Workbook, I would like to Copy all of the Rows in a Table tbl_BooksSource

Copy From:
Sheet1: Import
Table: tbl_BooksSource
Columns: D thru N only
Rows: All (except Heading)
Note: Columns A - C are not part of the table.


Copy To:
Sheet2: Books
Table: tbl_Books
Copy (append) to end of Table


Any guidance would be greatly appreciated…

 

[James006]

Hi,

Jan Karel Pieterse has an excellent explanation ... see link below :

VBA and Tables

 

[zero269]

Hi,

Jan Karel Pieterse has an excellent explanation ... see link below :

VBA and Tables

Thanks, James.

Surprisingly I stumbled upon that prior to posting my question. Unfortunately, I'm just beginning to look into VBA so decoding it to fit my needs is futile for now. I'm versed on PowerShell and batch file scripting for managing Windows systems, but new to VBA.

As a LinkedIn Learning subscriber (free through my library membership ) I plan to watch the following courses:

• Visual Basic Essential Training (5h 4m)
• Excel: Macros and VBA for Beginners (37m)

However, I'm hoping to get some help with building out this solution to help me better understand how to read the code.

I tried creating separate macros to see if I could stitch them together, but this is as far as I got:

Sub AddRowToTable()

Dim ws As Worksheet

Dim tbl As ListObject
Set ws = ActiveSheet

Set tbl = ws.ListObjects("tbl_Books")
tbl.ListRows.Add

End Sub
Sub CopyData()
'
' CopyData Macro
' Copy all rows from column D thru N only

'
    Range("D2:N2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
End Sub

Obviously, I need to stitch these two together in the right order and then find the missing piece that lets me Paste Values only in that newly created row at the End of the Table.

 

[zero269]

I've got it working by recording a Macro step-by-step.
However, I'm hoping that someone can take a look at it to see if there is a cleaner way to format it to make it more dynamic, such as using named variables.
For example, I'd like to run the macro without having to worry about what Sheet is currently active.
Thank you...

Here's what I got so far:

Sub CopyNewBooks()
'
' CopyNewBooks Macro
' Add new Row to bootom of tbl_Books on Books sheet
' Copy new books range from tbl_BookSource on Import sheet
' Paste Values in New Row in tbl_Books on Books sheet

    Sheets("Books").Select
    Range("tbl_Books[[#Headers],[Quiz]]").Select
    Selection.End(xlDown).Select
    Selection.EntireRow.Insert
    Sheets("Import").Select
    Range("D2:N2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Books").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A2").Select

End Sub

 

[James006]

Hello,

Below a test for your review

Sub CopyfromTable2Table()
' Declare your Variables
Dim wshBooks As Worksheet
Dim wshImport As Worksheet
Dim tblBooks As ListObject
Dim tblSourc As ListObject
Dim lastrow As Long

' Set your Variables
Set wshBooks = ThisWorkbook.Sheets(“Books”)
Set wshImport = ThisWorkbook.Sheets(“Import”)
Set tblBooks = wshBooks.ListObjects("tbl_Books")
Set tblSourc = wshImport.ListObjects("tbl_BookSource")

' Locate Last Row and add 1 for Destination at bottom
lastrow = tblBooks.Range.Rows.Count
lastrow = lastrow + 1

' Copy from Source to Destination
tblSourc.Range("D2:N2").Copy tblBooks.Range("A" & lastrow)

End Sub

 

[zero269]

Hello,

Below a test for your review

Sub CopyfromTable2Table()
' Declare your Variables
Dim wshBooks As Worksheet
Dim wshImport As Worksheet
Dim tblBooks As ListObject
Dim tblSourc As ListObject
Dim lastrow As Long

' Set your Variables
Set wshBooks = ThisWorkbook.Sheets(“Books”)
Set wshImport = ThisWorkbook.Sheets(“Import”)
Set tblBooks = wshBooks.ListObjects("tbl_Books")
Set tblSourc = wshImport.ListObjects("tbl_BookSource")

' Locate Last Row and add 1 for Destination at bottom
lastrow = tblBooks.Range.Rows.Count
lastrow = lastrow + 1

' Copy from Source to Destination
tblSourc.Range("D2:N2").Copy tblBooks.Range("A" & lastrow)

End Sub

Hi James,

Thanks for the VBA code. I tried it out in my SAMPLE workbook, and although it through up some errors in the beginning, I was able to remediate most of them doing the following:

• Added an e to the end of tblSource
• Added an s to tbl_BooksSource
• Strangely enough, I had to change all the double quotes to get past the Set your variables section. A system font difference apparently.

It's hanging up at the last line only now. However, I'm not sure how to remediate that one.

The values look correct for:

• tblSource.Range("D2:N2").Copy <<< has the correct range to Copy in the tbl_BooksSource on the Import sheet.
• tblBooks.Range("A" & lastrow) <<< has the correct destination column to paste in tbl_Books on the Books sheet.

Should there be anything between the above two values, such as a comma, or period...?

Your modified code I'm running:

Sub CopyfromTable2Table()
' Declare your Variables
Dim wshBooks As Worksheet
Dim wshImport As Worksheet
Dim tblBooks As ListObject
Dim tblSource As ListObject
Dim lastrow As Long

' Set your Variables
Set wshBooks = ThisWorkbook.Sheets("Books")
Set wshImport = ThisWorkbook.Sheets("Import")
Set tblBooks = wshBooks.ListObjects("tbl_Books")
Set tblSource = wshImport.ListObjects("tbl_BooksSource")

' Locate Last Row and add 1 for Destination at bottom
lastrow = tblBooks.Range.Rows.Count
lastrow = lastrow + 1

' Copy from Source to Destination
tblSource.Range("D2:N2").Copy tblBooks.Range("A" & lastrow)

End Sub

 

[James006]

You can test :

 tblSource.Range("D2:N2").Copy destination:=tblBooks.Range("A" & lastrow)

 

[Alex Blakenburg]

Give this a try:
It assumes D2:N2 is the first data row in tblSource, change the 1 in ListRows(1) if that is not the case.
It also assumes that the number of columns in both tables is the same.

Sub CopyfromTable2Table
' Declare your Variables
Dim wshBooks As Worksheet
Dim wshImport As Worksheet
Dim tblBooks As ListObject
Dim tblSource As ListObject
Dim tblrowNew As ListRow

' Set your Variables
Set wshBooks = ThisWorkbook.Sheets("Books")
Set wshImport = ThisWorkbook.Sheets("Import")
Set tblBooks = wshBooks.ListObjects("tbl_Books")
Set tblSource = wshImport.ListObjects("tbl_BooksSource")

' Locate Last Row and add 1 for Destination at bottom
Set tblrowNew = tblBooks.ListRows.Add

' Copy from Source to Destination
tblSource.ListRows(1).Range.Copy tblrowNew.Range

End Sub

 

[zero269]

You can test :

 tblSource.Range("D2:N2").Copy destination:=tblBooks.Range("A" & lastrow)

Hi James,

Thank you for the follow-up. Just saw your reply. I replaced the old code with your new code but getting the same results:
I wouldn't worry too much about any further remediation considering I've got a functional code that working for now... unless you're just curious; I'll test it and report back.

Sub CopyfromTable2Table()
' Declare your Variables
Dim wshBooks As Worksheet
Dim wshImport As Worksheet
Dim tblBooks As ListObject
Dim tblSource As ListObject
Dim lastrow As Long

' Set your Variables
Set wshBooks = ThisWorkbook.Sheets("Books")
Set wshImport = ThisWorkbook.Sheets("Import")
Set tblBooks = wshBooks.ListObjects("tbl_Books")
Set tblSource = wshImport.ListObjects("tbl_BooksSource")

' Locate Last Row and add 1 for Destination at bottom
lastrow = tblBooks.Range.Rows.Count
lastrow = lastrow + 1

' Copy from Source to Destination
 tblSource.Range("D2:N2").Copy Destination:=tblBooks.Range("A" & lastrow)

End Sub

 

[zero269]

It assumes D2:N2 is the first data row in tblSource, change the 1 in ListRows(1) if that is not the case.
It also assumes that the number of columns in both tables is the same.

Hi Alex,

Yes, D2 represents the first data row in the Table below the Header row; Table Row 2.

The Destination Table has more columns than the Source Table in my original file, although in my SAMPLE workbook it actually has fewer columns which isn't a concern because the COPY process expands the destination table.

One thing to note is that it only copied Table Row 2. Not all of the Table Rows from 2 down.

Source Table: Import

Destination Table: Books