Hi again Kennedy,
I had already done some work on this prior to seeing your reply to my
questions. It now appears that you have 6 further Child columns and I have no
idea what is in them.
Anyway, I have posted code below. The professional way to manipulate data is
to not change the source data sheet but to have a source data sheet and a
destination or output data sheet.
The following code does the above. However, you need to get back to the
source data prior to counting children and inserting lines. The code provides
the required lines and counts the children. My understanding of your original
data was as follows. (I hope you kept a backup of the original data.)
Col A: Name
Col B: Address
Col C: City
Col D: St
Col E: Child1Name
Col F: Child1DOB
Col G: Child2Name
Col H: Child2DOB
Col I: Child3Name
Col J: Child3DOB
Col K: Child4Name
Col L: Child4DOB
The code follows the above with as many ChildName and Child DOB as you have.
I have tried to keep the code as generic as possible so that you can edit
the Source and Destination sheet names in only one place plus the column Id
of the first childname if it is not column E. (See my comments in the code re
this).
See my comment re optional formatting code for the number of children. This
format will display the number of children like the following.
4 Children
The formatted cells are still like normal numeric cells and the data can be
used in maths functions such as summing etc. It is only the format that is
changed like changing currency format.
Feel free to get back to me if it does not do what you want.
Note that a space and underscore at the end of a line is a line break in an
otherwise single line of code.
If when you copy the code into you VBA editor, any of the lines are red,
then there is a line break where there should not be. You will need to edit
the code by deleting at the end of a line to bring lines together.
Sub MoveNames()
Dim wsSource As Worksheet
Dim wsDestin As Worksheet
Dim rngSource As Range
Dim c As Range
Dim tempCol As Long
Dim firstChildCol As Long
Dim parentCols As Long
Dim countChild As Long
Dim destRow As Long
'Edit "Sheet1" to name of source data sheet.
Set wsSource = Sheets("Sheet1")
'Edit "Sheet2" to name of destination data sheet.
Set wsDestin = Sheets("Sheet2")
'Edit "E" to column of first child name.
firstChildCol = wsSource.Columns("E").Column
parentCols = firstChildCol - 1
With wsSource
'Set rngSource to first column of source.
Set rngSource = .Range(.Cells(2, "A"), _
.Cells(.Rows.Count, "A").End(xlUp))
End With
For Each c In rngSource.Rows
With wsSource
tempCol = firstChildCol
'Find next destination parent row
'after last data in first child column
With wsDestin
destRow = .Cells(.Rows.Count, tempCol) _
.End(xlUp).Offset(1, 0).Row
End With
'Copy and paste parent data
.Range(c, c.Offset(0, parentCols - 1)).Copy _
wsDestin.Cells(destRow, "A")
'Copy each child name,DOB and count children
countChild = 0
Do
If .Cells(c.Row, tempCol) <> "" Then
.Range(.Cells(c.Row, tempCol), _
.Cells(c.Row, tempCol + 1)).Copy _
wsDestin.Cells(destRow + countChild, _
firstChildCol)
countChild = countChild + 1
tempCol = tempCol + 2
Else
Exit Do
End If
Loop
'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
End With
Next c
wsDestin.Columns.AutoFit
wsDestin.Select
End Sub
