Populating a table from a database based on birthdate

[iain9090]

Hi, I'm new to this and a relative novice when it comes to excel. I would greatly appreciate any help.

In my workbook, on sheet 1, I have my main database. A list of names with corresponding birth dates and other information.
On sheet 2 I wish to populate a table of a number of these names, based on their birthdate.

For example, anyone born between 01-Jan-1994 and 31-Dec-1995, will appear in this table on sheet 2.

Is this possible to do?

Thanks in advance

 

[ParamRay]

.
.

Simplest way would by to use an AutoFilter and copy the filtered data to Sheet2...

Or, if you want a macro, which column contains your birth dates?

 

[iain9090]

Column A contains 'Surname', column B 'Forename' and column C 'Birth date'

 

[iain9090]

Does anyone have any advice on creating a macro for this?

 

[ParamRay]

.
.

Place the following code in a standard code module in your workbook.

Sub Test()

    Dim Sht1 As Worksheet
    Dim Sht2 As Worksheet
    Dim Rng As Range
    Dim Cell As Range
    Dim Lb As Long
    Dim Ub As Long
    Dim Cnt As Long
    Dim i As Long
    
    'Set worksheet variables
    With ThisWorkbook
        Set Sht1 = .Worksheets("Sheet1")    'change as necessary
        Set Sht2 = .Worksheets.Add
    End With
    
    'Set range containing dates
    With Sht1
        Set Rng = Range(.Range("C2"), _
            .Range("C2").End(xlDown))
    End With
        
    'Copy field headings
    Sht2.Range("A1:C1").Value = _
        Sht1.Range("A1:C1").Value
    
    'Set criteria for dates
    Lb = DateSerial(1994, 1, 1)
    Ub = DateSerial(1995, 12, 31)
    
    'Loop through dates and copy
    'records that meet criteria...
    
    Cnt = 1
    For Each Cell In Rng
        If CLng(Cell.Value) >= Lb And _
            CLng(Cell.Value) <= Ub Then
            Cnt = Cnt + 1
            For i = 1 To 3
                Sht2.Cells(Cnt, i).Value = _
                    Cell.Offset(0, i - 3).Value
            Next i
        End If
    Next Cell
    
End Sub

 

[iain9090]

Thanks very much