Macro to concatenate cells into a single cell multiple times

[psymson]

Hi
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. J
I need to automate the creation of test scripts, which basically consists of taking the contents data from a row of cells, 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
1	Col 1	Col 2	Col 3
2	B3	C3	D3
3	B4	C4	D4
4	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 (B3)	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)% (B5, C5, D5)
		1
		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: (B3)
Test 3	SQL to confirm (B5)	PR	Data Migratedtyle="width: 224px; height: 20px;">Step 1
		2	Step 2
		3	Field: (B3)

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.

Andy

[Leith Ross]

Hello Andy,

It would be handy to have a copy of the workbook to develop and test the code with. Can you post a copy of your workbook?

[psymson]

Hi Leith

Thanks for the reply.

I am a newbie to this forum, how can I post an excel doc on here please?

Thanks