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>

Col A	Col B
flow1	subflow1
flow1	subflow2
flow1	subflow3
flow2	subflow4
flow2	subflow5

<tbody>
</tbody>

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

Col A	Col B	Flow 1	Flow 1	Flow1	Flow 2	Flow2
		subflow1	subflow2	subflow3	subflow4	subflow5
prod1	info	n	n	n	n	n
prod2	info	n	n	n	n	n

<tbody>
</tbody>

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