Hi Kennedy,

Sorry it has taken so long to get back to you but we are probably in
different time zones.

This is purely a hobby for an ageing retiree and I don't take on the
responsibility of paid work. I have the odd (and sometimes frequent) Seniors
Moment but generally I enjoy the challenge and it keeps my mind active.

The following code tests for additional cells with data to the right of
tempCol. If you have additional data out to the right that you do not want
included then we have the option of getting rid of it off the Source data
sheet or we will have to re-think the options.

Then there is the problem of the blank child name cell. The code relies on
there being data in the child name to find the next row on the destination
sheet. Therefore I have tested for data to the right of tempCol and if exists
then test tempCol for data and if empty then insert dummy data on the source
worksheet. I have used TBA (To Be Advised).


Do
'Test if any more data on the row
'between tempCol and far right of sheet.
If WorksheetFunction. _
CountA(.Range(.Cells(c.Row, tempCol), _
.Cells(c.Row, .Columns.Count))) 0 Then

'Insert character/s into blank cells
If .Cells(c.Row, tempCol) = "" Then
'Edit "TBA" to whatever you like
.Cells(c.Row, tempCol) = "TBA"
End If

.Range(.Cells(c.Row, tempCol), _
.Cells(c.Row, tempCol + 6)).Copy _
wsDestin.Cells(destRow + countChild, _
firstChildCol)
countChild = countChild + 1 'Remove if not required
tempCol = tempCol + 7
Else
Exit Do
End If

Loop


Now the entire section shown below can be removed to remove the child count.

'Insert child count
With wsDestin
'Following format line is optional.
.Cells(.Rows.Count, firstChildCol) _
.End(xlUp).Offset(1, 0) _
.NumberFormat = "0"" Children"""

.Cells(.Rows.Count, firstChildCol) _
.End(xlUp).Offset(1, 0) = countChild

End With

--
Regards,

OssieMac