Bring data split between rows into one row (have one row per record)

Hello All,

This probably needs some sort of VBA code - and I am not up speed with VBA! Here's the situation:

(1) Workbook has many worksheets
(2) Each worksheet contains employee records for a specific company - unfortunately each employee record is split between 2 or more adjacent rows rather than being fully on one row
(3) I need to pull data from all worksheets (companies) onto one main tab and have each employee record on one row (from pt 2 above right now data for each employee is split between 2 or more rows)

I attach a small sample Excel file showing 2 worksheets and the main tab where data needs to be populated - with some explanatory notes. All employee names and details in the sample file are fictitious - I guarantee!

Please if you guys could guide me on how I can achieve this - in reality I have at least 20 workbooks and each workbook contains at least 20 worksheets and each worksheet may have 100s of employee records split between rows.

Thanks so much all.

Yusuf

Hi Orrin,

Fantastic! The code works wonderfully - dried up many tears of despair. Thank you so much!

Just for my learning - have a couple of questions on the coding:

1. At the start of the sub, you initially assign r = 3 - what would be the logic behind using 3 as starting value - can this be any number of my choice or is this somehow tied into the column B:B and "Spouse"?

2. Towards end of the sub, you have "If Not F Is Nothing And F.Row > r + 1 Then GoTo OrgDo". What is the logic for using "And F.Row > r +1"? How would this stop code from going to OrgDo each time?

Once again your help is much appreciated.

Best,

Yusuf