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:

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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]

Please Login or Register  to view this content.

[Ramtrap]

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

Can I change the:

Please Login or Register  to view this content.

to something like:

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.