VBA Copy/Paste data from one workbook to a table in another workbook

[Ramtrap]

I have a module that I've developed with the help of various communities whereby I want to take information from one worksheet in one workbook (or rather several workbooks) and throw all that information to another workbook (a master). I get this to work just fine if it's *not* in a table, however, I want to put it on the table. The issue I'm having stems, I believe, from this line within my code: (j = .Cells(.Rows.Count, "A").End(xlUp).Row + 1).

Here's a bulk of the code to put it into perspective, with comments on what it's doing:

...

' This code will find the last row of information that has data in it
' When a table is present, however, even if there are blank rows, 
' this script treats those as 'data' fields and will go to the line UNDER the table

With Workbooks("MasterWB.xlsm").Worksheets("Sheet1")
    j = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With

' This code will copy the data from the slave worksheet and paste to master

For i = 1 To LastRowSlave
    With Workbooks("Slave1.xlsm").Worksheets(strName)                                              'strName is just the ActiveSheet.Name
        If .Cells(i, 1).Value = "x" Then                                                                         'if there is an x in column A, it's marking it as data that needs to be moved to the master
            .Rows(i).Copy Destination:=Workbooks("MasterWB.xlsm").Worksheets("Sheet1).Range("A" & j)
            .Cells(i, 1).Value = "Added on " & Date                                                          'This replaces the 'x' with a date the info was added so as to prevent manual marking of these fields
            j = j + 1
        End If
    End With
Next i

...

So the biggest question, if anyone can help: How can I paste that information into a TABLE on the MasterWB.xlsm spreadsheet?

Additionally (and probably off topic, but just to pose the question), is there an easy way to identify rows that may have been edited? My worry is that they edit one block on the slave worksheet and even if they manually place an 'x' in that Column A, it will only add a brand new row to my Master spreadsheet, thus double-counting that row. Is there a simpler solution?

Any help is appreciated. Thank you!

[AlphaFrog]

This will add a row to the bottom of a table (ListObject) and paste the data in the new row.

It also checks if there is a match in column B (assuming that has a unique value for each row). If a match id found, it pastes the data there instead of adding a new row.

Dim tbl As ListObject
Dim rngFound As Range

Set tbl = Workbooks("MasterWB.xlsm").Worksheets("Sheet1").ListObjects(1)

' This code will copy the data from the slave worksheet and paste to master
For i = 1 To LastRowSlave
    With Workbooks("Slave1.xlsm").Worksheets(strName) 'strName is just the ActiveSheet.Name
        If .Cells(i, 1).Value = "x" Then 'if there is an x in column A, it's marking it as data that needs to be moved to the master
        
            'Look for a match in column B
            Set rngFound = tbl.ListColumns(2).Range.Find(.Range("B" & i), , xlValues, xlWhole, 1, 1, 0)
            If Not rngFound Is Nothing Then
                'Replace existing row
                .Rows(i).Copy Destination:=rngFound.EntireRow
            Else
                'Add to bottom of the table
                tbl.ListRows.Add
                .Rows(i).Copy Destination:=tbl.ListRows(tbl.ListRows.Count).Range.EntireRow
            End If
            
            .Cells(i, 1).Value = "Added on " & Date 'This replaces the 'x' with a date the info was added so as to prevent manual marking of these fields
        End If
    End With
Next i

This site is helpful for Table syntax in VBA.
https://www.thespreadsheetguru.com/b...t-excel-tables

[Ramtrap]

[QUOTE=AlphaFrog;4871366]This will add a row to the bottom of a table (ListObject) and paste the data in the new row.

It also checks if there is a match in column B (assuming that has a unique value for each row). If a match id found, it pastes the data there instead of adding a new row.

Dim tbl As ListObject
Dim rngFound As Range

Set tbl = Workbooks("MasterWB.xlsm").Worksheets("Sheet1").ListObjects(1)

' This code will copy the data from the slave worksheet and paste to master
For i = 1 To LastRowSlave
    With Workbooks("Slave1.xlsm").Worksheets(strName) 'strName is just the ActiveSheet.Name
        If .Cells(i, 1).Value = "x" Then 'if there is an x in column A, it's marking it as data that needs to be moved to the master
        
            'Look for a match in column B
            Set rngFound = tbl.ListColumns(2).Range.Find(.Range("B" & i), , xlValues, xlWhole, 1, 1, 0)
            If Not rngFound Is Nothing Then
                'Replace existing row
                .Rows(i).Copy Destination:=rngFound.EntireRow
            Else
                'Add to bottom of the table
                tbl.ListRows.Add
                .Rows(i).Copy Destination:=tbl.ListRows(tbl.ListRows.Count).Range.EntireRow
            End If
            
            .Cells(i, 1).Value = "Added on " & Date 'This replaces the 'x' with a date the info was added so as to prevent manual marking of these fields
        End If
    End With
Next i

The result you gave is great, but I'm having another issue now when I run the code to copy and paste into the table. It is pasting the contents (e.g. formula) rather than the actual value of the cell. This is causing havoc when multiple people would update.
Example, say I have two people, Jones and Smith, each updating and passing that info to a master spreadsheet. The keys to each of them look like this:

=Left($D3,5)&Row(1:1)

This gives me results of JONES1 and SMITH1, respectively. When I ingest the information from JONES, it's fine. When I ingest from SMITH, it appears fine on the surface (i.e., no errors), but it's keeping that formula, so it's applying the formula as it stands now on the Master spreadsheet.

Is there an easy way to take the actual values of the cells I am copying over, rather than the formula?

[AlphaFrog]

Dim tbl As ListObject
Dim rngFound As Range

Set tbl = Workbooks("MasterWB.xlsm").Worksheets("Sheet1").ListObjects(1)

' This code will copy the data from the slave worksheet and paste to master
For i = 1 To LastRowSlave
    With Workbooks("Slave1.xlsm").Worksheets(strName) 'strName is just the ActiveSheet.Name
        If .Cells(i, 1).Value = "x" Then 'if there is an x in column A, it's marking it as data that needs to be moved to the master
        
            'Look for a match in column B
            Set rngFound = tbl.ListColumns(2).Range.Find(.Range("B" & i), , xlValues, xlWhole, 1, 1, 0)
            If Not rngFound Is Nothing Then
                'Replace existing row
                .Rows(i).Copy
                rngFound.EntireRow.PasteSpecial xlPasteValues
            Else
                'Add to bottom of the table
                tbl.ListRows.Add
                .Rows(i).Copy
                tbl.ListRows(tbl.ListRows.Count).Range.EntireRow.PasteSpecial xlPasteValues
            End If
            
            .Cells(i, 1).Value = "Added on " & Date 'This replaces the 'x' with a date the info was added so as to prevent manual marking of these fields
        End If
    End With
Next i
Application.CutCopyMode = False

[Ramtrap]

Dim tbl As ListObject
Dim rngFound As Range

Set tbl = Workbooks("MasterWB.xlsm").Worksheets("Sheet1").ListObjects(1)

' This code will copy the data from the slave worksheet and paste to master
For i = 1 To LastRowSlave
    With Workbooks("Slave1.xlsm").Worksheets(strName) 'strName is just the ActiveSheet.Name
        If .Cells(i, 1).Value = "x" Then 'if there is an x in column A, it's marking it as data that needs to be moved to the master
        
            'Look for a match in column B
            Set rngFound = tbl.ListColumns(2).Range.Find(.Range("B" & i), , xlValues, xlWhole, 1, 1, 0)
            If Not rngFound Is Nothing Then
                'Replace existing row
                .Rows(i).Copy
                rngFound.EntireRow.PasteSpecial xlPasteValues
            Else
                'Add to bottom of the table
                tbl.ListRows.Add
                .Rows(i).Copy
                tbl.ListRows(tbl.ListRows.Count).Range.EntireRow.PasteSpecial xlPasteValues
            End If
            
            .Cells(i, 1).Value = "Added on " & Date 'This replaces the 'x' with a date the info was added so as to prevent manual marking of these fields
        End If
    End With
Next i
Application.CutCopyMode = False

Thank you very much for the above!!! I have one more issue to consider, now. Correct me if I'm wrong, but I believe because I'm doing the following, it's copying the entire row, and when it pastes, because it's pasting to a table, every column past the end of my table (just say Column Z) is being re-appropriated as a column that belongs for the table (thus, columns AA:XFD). Is there a way to change the "EntireRow" to a range? I've tried, but can't figure out the algorithm so that it's just Columns A:Z.

            ...
            'Look for a match in column B
            Set rngFound = tbl.ListColumns(2).Range.Find(.Range("B" & i), , xlValues, xlWhole, 1, 1, 0)
            If Not rngFound Is Nothing Then
                'Replace existing row
                .Rows(i).Copy
                rngFound.EntireRow.PasteSpecial xlPasteValues
            Else
                'Add to bottom of the table
                tbl.ListRows.Add
                .Rows(i).Copy
                tbl.ListRows(tbl.ListRows.Count).Range.EntireRow.PasteSpecial xlPasteValues
            End If
            ...

Can I change the:

rngFound.EntireRow.PasteSpecial xlPasteValues

to something like:

rngFound.Range((1,1),26,1)).PasteSpecial xlPasteValues[/B]

Or something to that effect, so that when it pastes the entire row to my master table, it doesn't create a bunch of blank columns?

Thank you!

[AlphaFrog]

This copies columns B to Z from row i and pastes it to the table. Change the copy-from column letters in B1:Z1 to suit. Don't change the number 1. The PasteSpecial part will auto-size to the copied range.

            'Look for a match in column B
            Set rngFound = tbl.ListColumns(2).Range.Find(.Range("B" & i), , xlValues, xlWhole, 1, 1, 0)
            If Not rngFound Is Nothing Then
                'Replace existing row
                .Rows(i).Range("B1:Z1").Copy
                rngFound.PasteSpecial xlPasteValues
            Else
                'Add to bottom of the table
                tbl.ListRows.Add
                .Rows(i).Range("B1:Z1").Copy
                tbl.ListRows(tbl.ListRows.Count).Range.PasteSpecial xlPasteValues
            End If