Hi,

I am trying to write a VBA script which will copy over a dynamic number of rows and columns from one table to another table based on a unique key in the first column of each table

I have attached an example file which has the two tables I would like to join. The 'Data Sheet' would start with just the three columns (unique key, manager, and role) and only ever have 3 columns. The 'merge from sheet' has the unique key, and more info about the employee (there is not a set number of columns here, but it will always be bookended by the unique key columns). There is also a matrix on that sheet which I do not want to include in the merge.

[TABLE="width: 395"]

[TD="colspan: 4"]**Add to manager_info table**[/TD]

[TD="align: center"]Unique Key[/TD]

[TD="align: center"]Employee[/TD]

[TD="align: center"]Salary[/TD]

[TD="align: center"]Number[/TD]

[TD="align: center"]Hire Date[/TD]

[TD="align: center"]1[/TD]

[TD="align: center"]Bobby[/TD]

[TD="align: center"]$10[/TD]

[TD="align: center"]3234[/TD]

[TD="align: center"]1/1/2014[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]Rockey[/TD]

[TD="align: center"]$11[/TD]

[TD="align: center"]3245[/TD]

[TD="align: center"]2/2/2014[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]Tommy[/TD]

[TD="align: center"]$10[/TD]

[TD="align: center"]3256[/TD]

[TD="align: center"]2/4/2014[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]Billy[/TD]

[TD="align: center"]$20[/TD]

[TD="align: center"]3267[/TD]

[TD="align: center"]4/5/2015[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]Mindy[/TD]

[TD="align: center"]$100[/TD]

[TD="align: center"]3278[/TD]

[TD="align: center"]10/10/1950[/TD]

[/TABLE]

Data Sheet: [TABLE="width: 500"]

[TD="colspan: 3"]* Before Macro is Run*

[/TD]

[TD="colspan: 4"]**Added from Macro**

[/TD]

Unique Key

Manager

Role

Employee

Salary

Number

Hire Date

[TD="align: right"]1

[/TD]

Bill

Builder

Bobby

[TD="align: right"]$10

[/TD]

[TD="align: right"]3234

[/TD]

[TD="align: right"]1/1/2014

[/TD]

[TD="align: right"]1

[/TD]

Bill

Fixer

Bobby

[TD="align: right"]$10

[/TD]

[TD="align: right"]3234

[/TD]

[TD="align: right"]1/1/2014

[/TD]

[TD="align: right"]1

[/TD]

Bill

Cleaner

Bobby

[TD="align: right"]$10

[/TD]

[TD="align: right"]3234

[/TD]

[TD="align: right"]1/1/2014

[/TD]

[TD="align: right"]2

[/TD]

Ted

Doer

Rockey

[TD="align: right"]$11

[/TD]

[TD="align: right"]3245

[/TD]

[TD="align: right"]2/2/2014

[/TD]

[TD="align: right"]3

[/TD]

Loise

Shopper

Tommy

[TD="align: right"]$10

[/TD]

[TD="align: right"]3256

[/TD]

[TD="align: right"]2/4/2014

[/TD]

[TD="align: right"]3

[/TD]

Loise

Planner

Tommy

[TD="align: right"]$10

[/TD]

[TD="align: right"]3256

[/TD]

[TD="align: right"]2/4/2014

[/TD]

[TD="align: right"]3

[/TD]

Loise

Fetcher

Tommy

[TD="align: right"]$10

[/TD]

[TD="align: right"]3256

[/TD]

[TD="align: right"]2/4/2014

[/TD]

[TD="align: right"]4

[/TD]

Kate

Driver

Billy

[TD="align: right"]$20

[/TD]

[TD="align: right"]3267

[/TD]

[TD="align: right"]4/5/2015

[/TD]

[TD="align: right"]5

[/TD]

Rothgaur

Security

Mindy

[TD="align: right"]$100

[/TD]

[TD="align: right"]3278

[/TD]

[TD="align: right"]10/10/1950

[/TD]

[/TABLE]

I have found and adapted some code which does a vlookup to bring just the first column. I thought about adapting with a loop to add each column row by row, but I think that would be very time consuming...a match, select and copy/paste may be faster/lighter? Maybe there is another way I don't know about (collections or something)?

Note: This will be part of a series of macros which convert the matrix (employees names linked to roles with the X) into a pivot table, the end goal being to have a list of Managers, their employees, and which roles they have.

Thank you so much for any help you can give!!