VBA Code to transpose data from one sheet (rows) to another as columns, adding new colums as required

[genuneto]

I'm currently working on a spreadsheet for capacity planning and trying to implement some automation to it (you know, in order to avoid users messing up with the model too much).
In one sheet, the users can include business flows and sub-flows (2 columns, n rows). This should feed another sheet with columns to be filled with product information for each flow.

Example:

Source Sheet:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">
</code>[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Col A[/TD]
[TD]Col B[/TD]
[/TR]
[TR]
[TD]flow1[/TD]
[TD]subflow1[/TD]
[/TR]
[TR]
[TD]flow1[/TD]
[TD]subflow2[/TD]
[/TR]
[TR]
[TD]flow1[/TD]
[TD]subflow3[/TD]
[/TR]
[TR]
[TD]flow2[/TD]
[TD]subflow4[/TD]
[/TR]
[TR]
[TD]flow2[/TD]
[TD]subflow5[/TD]
[/TR]
</tbody>[/TABLE]
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">










Destination Sheet:

</code>[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Col A[/TD]
[TD]Col B[/TD]
[TD]Flow 1[/TD]
[TD]Flow 1[/TD]
[TD]Flow1[/TD]
[TD]Flow 2[/TD]
[TD]Flow2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]subflow1[/TD]
[TD]subflow2[/TD]
[TD]subflow3[/TD]
[TD]subflow4[/TD]
[TD]subflow5[/TD]
[/TR]
[TR]
[TD]prod1[/TD]
[TD]info[/TD]
[TD]n[/TD]
[TD]n[/TD]
[TD]n[/TD]
[TD]n[/TD]
[TD]n[/TD]
[/TR]
[TR]
[TD]prod2[/TD]
[TD]info[/TD]
[TD]n[/TD]
[TD]n[/TD]
[TD]n[/TD]
[TD]n[/TD]
[TD]n[/TD]
[/TR]
</tbody>[/TABLE]
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">

</code><code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">

</code>



The idea is, when new flows are added to Source sheet, the columns are automatically added to the destination sheet, merging the cells that contain equal values. In the example above, "flow1" header would be merged.

Is there any way to achieve this? I'm working on Excel 2013.

Thanks in advance!