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"]

[tr]

[td][/td]

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

[/tr]

[tr]

[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]

[/tr]

[tr]

[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]

[/tr]

[tr]

[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]

[/tr]

[tr]

[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]

[/tr]

[tr]

[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]

[/tr]

[tr]

[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]

[/tr]

[/TABLE]

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

[tr]

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

[/TD]

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

[/TD]

[/tr]

[tr]

[td]

Unique Key

[/td]

[td]

Manager

[/td]

[td]

Role

[/td]

[td]

Employee

[/td]

[td]

Salary

[/td]

[td]

Number

[/td]

[td]

Hire Date

[/td]

[/tr]

[tr]

[TD="align: right"]1

[/TD]

[td]

Bill

[/td]

[td]

Builder

[/td]

[td]

Bobby

[/td]

[TD="align: right"]$10

[/TD]

[TD="align: right"]3234

[/TD]

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

[/TD]

[/tr]

[tr]

[TD="align: right"]1

[/TD]

[td]

Bill

[/td]

[td]

Fixer

[/td]

[td]

Bobby

[/td]

[TD="align: right"]$10

[/TD]

[TD="align: right"]3234

[/TD]

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

[/TD]

[/tr]

[tr]

[TD="align: right"]1

[/TD]

[td]

Bill

[/td]

[td]

Cleaner

[/td]

[td]

Bobby

[/td]

[TD="align: right"]$10

[/TD]

[TD="align: right"]3234

[/TD]

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

[/TD]

[/tr]

[tr]

[TD="align: right"]2

[/TD]

[td]

Ted

[/td]

[td]

Doer

[/td]

[td]

Rockey

[/td]

[TD="align: right"]$11

[/TD]

[TD="align: right"]3245

[/TD]

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

[/TD]

[/tr]

[tr]

[TD="align: right"]3

[/TD]

[td]

Loise

[/td]

[td]

Shopper

[/td]

[td]

Tommy

[/td]

[TD="align: right"]$10

[/TD]

[TD="align: right"]3256

[/TD]

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

[/TD]

[/tr]

[tr]

[TD="align: right"]3

[/TD]

[td]

Loise

[/td]

[td]

Planner

[/td]

[td]

Tommy

[/td]

[TD="align: right"]$10

[/TD]

[TD="align: right"]3256

[/TD]

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

[/TD]

[/tr]

[tr]

[TD="align: right"]3

[/TD]

[td]

Loise

[/td]

[td]

Fetcher

[/td]

[td]

Tommy

[/td]

[TD="align: right"]$10

[/TD]

[TD="align: right"]3256

[/TD]

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

[/TD]

[/tr]

[tr]

[TD="align: right"]4

[/TD]

[td]

Kate

[/td]

[td]

Driver

[/td]

[td]

Billy

[/td]

[TD="align: right"]$20

[/TD]

[TD="align: right"]3267

[/TD]

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

[/TD]

[/tr]

[tr]

[TD="align: right"]5

[/TD]

[td]

Rothgaur

[/td]

[td]

Security

[/td]

[td]

Mindy

[/td]

[TD="align: right"]$100

[/TD]

[TD="align: right"]3278

[/TD]

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

[/TD]

[/tr]

[/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!!

forum.ozgrid.com/index.php?attachment/64720/