Macro for concatenating and moving data

Hi guys.

I have been given a task at work and am after some assistance, or at least a point in the right direction if possible please.

I need to automate the creation of test scripts, which basically consists of taking the contents data from a row of cells in Excel 2010, concatenating them together, then placing it into the middle of a string of text in another cell in another workbook, then replicating this several times.

For example the initial table will look something like this:

A B C
2 Col 1 Col 2 Col 3
3 B3 C3 D3
4 B4 C4 D4
5 B5 C5 D5


Then the test script template will look something like this (in a separate Excel workbook).

Test Name Test Description Step Step Description

Test 1 SQL to confirm () PR Data Migrated ()

1 Step 1
2 Step 2
3 Field: ()

Test 2 SQL to confirm () PR Data Migrated ()

1 Step 1
2 Step 2
3 Field: ()

Test 3 SQL to confirm () PR Data Migrated ()

1 Step 1
2 Step 2
3 Field: ()


And the end result will look something like this (will be a bit more involved with around 1000 or so rows / Tests) but to give you an idea).


Test Name Test Description Step Step Description

Test 1 SQL to confirm (B3) PR Data Migrated (B3,C3,D3)

1 Step 1
2 Step 2
3 Field: (B3)

Test 2 SQL to confirm (B4) PR Data Migrated (B4,C4,D4)

1 Step 1
2 Step 2
3 Field: (B4)

Test 3 SQL to confirm (B5) PR Data Migrated (B5,C5,D5)

1 Step 1
2 Step 2
3 Field: (B5)

I am thinking a VBA macro will be the best / only way to achieve this, due to the amount of rows of data, in order to save time doing it manually and can then also be used in the future.

Any ideas will be VERY much appreciated.

Thanks for taking the time to at least read this.

(Edit: apologies as not formatting the tables correctly on this forum for some reason no matter how I space the contents)

Andy