find and copy rows based on dates in two columns

[Guest]

Hi
Sheet01 contains details of club members. Now I need a makro to find members
who were active on a certain date (or a perid), and copy those rows to Sheet02

Sheet01 has EnterDate in Col 1, ExitDate in Col 2
From a userform on Sheet03 i plan to get two dates from user: LastExitDate
and FirstEnterDate.

I suppose logic should be:

ExitDate empty or => LastExitDate (dont want those who left earlier)
EnterDate <= FirstEnterDate (dont want those who entered after)

Writing a macro that solves this is beyond my skills - any help is very
appreciated
Thank you!
ulf

 

[Guest]

You need tot modify the constants StartDate and EndDate as required. You
also may need to changge the sheet names "Sheet1" and "Sheet2".

Sub movemembers()

Const StartDate As Date = "4/7/07"
Const EndDate As Date = "7/4/07"

With Sheets("Sheet2")
Sh2LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
End With
Sh2RowCount = Sh2LastRow + 1
With Sheets("Sheet1")
Sh1Lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
For Sh1RowCount = 1 To Sh1Lastrow
EnterDate = .Range("A" & Sh1RowCount)
ExitDate = .Range("B" & Sh1RowCount)
If IsDate(EnterDate) And IsDate(ExitDate) Then
If (EnterDate <= StartDate) And _
(ExitDate >= EndDate) Then

.Rows(Sh1RowCount).Copy Destination:= _
Sheets("Sheet2").Rows(Sh2RowCount)

End If
End If
Next Sh1RowCount
End With

End Sub

 

[Guest]

Thank you - that took me a long way.
One thing remains: all members have enter dates - but only some have exit
dates - these cells are empty

So I guess this has to be modified?

If IsDate(EnterDate) And IsDate(ExitDate) Then

If (EnterDate <= StartDate) And _
(ExitDate >= EndDate) Then

Regards
ulf

 

[Guest]

I don't think the code needs to be modified based on your original
instructions. Empty cells will not meet the isdate test and will not be
included.