Macro for concatenating and moving data [Closed]

Report
Posts
2
Registration date
Friday December 2, 2016
Status
Member
Last seen
December 5, 2016
-
Posts
2
Registration date
Friday December 2, 2016
Status
Member
Last seen
December 5, 2016
-
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

2 replies

Posts
15233
Registration date
Sunday June 8, 2008
Status
Contributor
Last seen
April 17, 2021
4
Are you familiar already with programming in general and with using VBA?
Did you already perform simpler exercices in VBA?
Do you want the destination workbook to contain a sequence of identical lines, except that some data is copied from the source workbook?
In that case, it is perhaps simpler to fully populate the destination workbook from the macro, rather than inserting data in the middle of the existing data.
Posts
2
Registration date
Friday December 2, 2016
Status
Member
Last seen
December 5, 2016

Hi

Thanks for the reply.

I am familiar with VBA and have written some more complex macro's in the past, although not recently and have only just got back into it.

Good point raised about it being easier to fully populate the destination cells with a macro, as the tests will just repeat, with the only variables being the data from the source workbook.

Any further help would be greatly appreciated.

Thanks again.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!