From: Risky Dave on

This feels like one of those things that is probably really easy but my VBA
obviously isn't up to the job :-(

On sheet "A" I have a column containing a list of names.
On sheet "B" I have the same list of names (not necessarily in the same
order) on a different sheet in the same workbook that has a load of other
data associated with it.

What I would like to do is sort the list on sheets A by the date of birth
data field associated with those names on sheet B.

Suggestions on what the code might look like would be appreciated.


From: joel on

Yo need to put the birthdate onto Sheet A and then sort. the code
below looks up each brithdate using column A as the ID field for each

Sub SortByBirthdates()

Set sht = Sheets("Sheet A")
Set BirthSht = Sheets("Sheet B")

With sht
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 1 To LastRow
'get ID field from Column A
ID = .Range("A" & RowCount)
'lookup ID in 2nd sheet

With Sheets("sheet B")
Set c = .Columns("A").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Cannot find ID : " & ID)
BirthDate = .Range("B" & c.Row)
sht.Range("B" & RowCount) = BirthDate
End If
End With
Next RowCount

'now sort by birth date
.Rows("1:" & LastRow).Sort _
header:=xlNo, _
key1:=.Range("B1"), _

End With

End Sub

joel's Profile: 229
View this thread: