Distribute single row data to multiple rows, with formatting

[joaobferre]

Hi Excel Community!

I need to distribute the values from a row, of certain external database (.xlsx file), to multiple rows, with VBA.

Database model:

Day	Time	Appointment	Complete Appointment	Process Number	Author	Defendant	Code	City	First Lawyer	Second Lawyer
10/05/2021	15:00	Meet	Meet with John	123.456.9898	Carl Dimitri	Cup Bank S/A	ZZT	Wawhington	André Luiz Navarro	André Luiz Navarro

All I want to do is get every value from that row, and distribute them like the image below.

At the end, the report looks like this (generates 4 rows for every single row from database):

At the moment, I'm doing it with Excel formulas, but I want to transform in VBA code, reducing the manual work and increase the productivity. 

Here's the code I'm using on the "Appointment" column, for example:

=IF(LEN(INDIRECT("DADOS!D"&INT(ROW()/4)+2))>=150; LEFT(INDIRECT("DADOS!D"&INT(ROW()/4)+2); 150) & "(...)"; INDIRECT("DADOS!D"&INT(ROW()/4)+2))

Also, I want to, for every new date in "Date" column, the VBA code inserts a blue filled row (same color and width as header), separating them.

For example:

Date

10/05/2021

10/05/2021

10/05/2021

#BLUE FILLED ROW#

10/06/2021

10/06/2021

#BLUE FILLED ROW#

10/07/2021

(...)

This last step is currently done manually.

Any suggestion? I don't even know how to search about it.

Thank you all!!!

[#2]

Hi @joaobferre,

The easiest solution what I can see here is to use the mail merge feature, that will skip all you hassles of make VBA code or Modern solution what I would propose you to use Power Automate to create a flow linking to the word document.

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer & like it to help the other members find it more.