Distribute single row data to multiple rows, with formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-2335494%22%20slang%3D%22en-US%22%3EDistribute%20single%20row%20data%20to%20multiple%20rows%2C%20with%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2335494%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Excel%20Community!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20distribute%20the%20values%20from%20a%20row%2C%20of%20certain%20external%20database%20(.xlsx%20file)%2C%20to%20multiple%20rows%2C%20with%20VBA.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDatabase%20model%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%221642%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2293px%22%20height%3D%2257px%22%3E%3CSTRONG%3EDay%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2265px%22%20height%3D%2257px%22%3E%3CSTRONG%3ETime%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22179px%22%20height%3D%2257px%22%3E%3CSTRONG%3EAppointment%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22294px%22%20height%3D%2257px%22%3E%3CSTRONG%3EComplete%20Appointment%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22167px%22%20height%3D%2257px%22%3E%3CSTRONG%3EProcess%20Number%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22126px%22%20height%3D%2257px%22%3E%3CSTRONG%3EAuthor%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22239px%22%20height%3D%2257px%22%3E%3CBR%20%2F%3E%3CSTRONG%3EDefendant%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2254px%22%20height%3D%2257px%22%3E%3CSTRONG%3ECode%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22101px%22%20height%3D%2257px%22%3E%3CSTRONG%3ECity%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22164px%22%20height%3D%2257px%22%3E%3CSTRONG%3EFirst%20Lawyer%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22159px%22%20height%3D%2257px%22%3E%3CSTRONG%3ESecond%20Lawyer%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2293px%22%20height%3D%2230px%22%3E10%2F05%2F2021%3C%2FTD%3E%3CTD%20width%3D%2265px%22%20height%3D%2230px%22%3E15%3A00%3C%2FTD%3E%3CTD%20width%3D%22179px%22%20height%3D%2230px%22%3EMeet%3C%2FTD%3E%3CTD%20width%3D%22294px%22%20height%3D%2230px%22%3EMeet%20with%20John%3C%2FTD%3E%3CTD%20width%3D%22167px%22%20height%3D%2230px%22%3E123.456.9898%3C%2FTD%3E%3CTD%20width%3D%22126px%22%20height%3D%2230px%22%3ECarl%20Dimitri%3C%2FTD%3E%3CTD%20width%3D%22239px%22%20height%3D%2230px%22%3ECup%20Bank%20S%2FA%3C%2FTD%3E%3CTD%20width%3D%2254px%22%20height%3D%2230px%22%3EZZT%3C%2FTD%3E%3CTD%20width%3D%22101px%22%20height%3D%2230px%22%3EWawhington%3C%2FTD%3E%3CTD%20width%3D%22164px%22%20height%3D%2230px%22%3EAndr%C3%A9%20Luiz%20Navarro%3C%2FTD%3E%3CTD%20width%3D%22159px%22%20height%3D%2230px%22%3EAndr%C3%A9%20Luiz%20Navarro%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20I%20want%20to%20do%20is%20get%20every%20value%20from%20that%20row%2C%20and%20distribute%20them%20like%20the%20image%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EAt%20the%20end%2C%20the%20report%20looks%20like%20this%20(generates%204%20rows%20for%20every%20single%20row%20from%20database)%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22imagem_2021-05-07_105722.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F279092i51F49A2CB3A8EDB7%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22imagem_2021-05-07_105722.png%22%20alt%3D%22imagem_2021-05-07_105722.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAt%20the%20moment%2C%20I'm%20doing%20it%20with%20Excel%20formulas%2C%20but%20I%20want%20to%20transform%20in%20VBA%20code%2C%20reducing%20the%20manual%20work%20and%20increase%20the%20productivity.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20the%20code%20I'm%20using%20on%20the%20%22Appointment%22%20column%2C%20for%20example%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(LEN(INDIRECT(%22DADOS!D%22%26amp%3BINT(ROW()%2F4)%2B2))%26gt%3B%3D150%3B%20LEFT(INDIRECT(%22DADOS!D%22%26amp%3BINT(ROW()%2F4)%2B2)%3B%20150)%20%26amp%3B%20%22(...)%22%3B%20INDIRECT(%22DADOS!D%22%26amp%3BINT(ROW()%2F4)%2B2))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20I%20want%20to%2C%20for%20every%20new%20date%20in%20%22Date%22%20column%2C%20the%20VBA%20code%20inserts%20a%20blue%20filled%20row%20(same%20color%20and%20width%20as%20header)%2C%20separating%20them.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3EDate%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E10%2F05%2F2021%3C%2FP%3E%3CP%3E10%2F05%2F2021%3C%2FP%3E%3CP%3E10%2F05%2F2021%3C%2FP%3E%3CP%3E%3CSTRONG%3E%23BLUE%20FILLED%20ROW%23%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E10%2F06%2F2021%3C%2FP%3E%3CP%3E10%2F06%2F2021%3C%2FP%3E%3CP%3E%3CSTRONG%3E%23BLUE%20FILLED%20ROW%23%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E10%2F07%2F2021%3C%2FP%3E%3CP%3E(...)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20last%20step%20is%20currently%20done%20manually.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20suggestion%3F%20I%20don't%20even%20know%20how%20to%20search%20about%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20all!!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2335494%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2338307%22%20slang%3D%22en-US%22%3ERe%3A%20Distribute%20single%20row%20data%20to%20multiple%20rows%2C%20with%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2338307%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1047935%22%20target%3D%22_blank%22%3E%40joaobferre%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20easiest%20solution%20what%20I%20can%20see%20here%20is%20to%20use%20the%20mail%20merge%20feature%2C%20that%20will%20skip%20all%20you%20hassles%20of%20make%20VBA%20code%20or%20Modern%20solution%20what%20I%20would%20propose%20you%20to%20use%20Power%20Automate%20to%20create%20a%20flow%20linking%20to%20the%20word%20document.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%23808080%22%3EIf%20you%20find%20the%20above%20solution%20resolved%20your%20query%20don't%20forget%20mark%20as%26nbsp%3B%3CSPAN%3EOfficial%2FBest%20Answer%20%26amp%3B%20like%20it%20to%20help%20the%20other%20members%20find%20it%20more.%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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:

 

DayTimeAppointmentComplete AppointmentProcess NumberAuthor
Defendant
CodeCityFirst LawyerSecond Lawyer
10/05/202115:00MeetMeet with John123.456.9898Carl DimitriCup Bank S/AZZTWawhingtonAndré Luiz NavarroAndré 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):

 

imagem_2021-05-07_105722.png

 

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

 

 

1 Reply

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.