find and copy rows based on dates in two columns
 

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

find and copy rows based on dates in two columns
 

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

"ulfb" wrote:

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

find and copy rows based on dates in two columns
 

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



"Joel" wrote:

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

"ulfb" wrote:

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

find and copy rows based on dates in two columns
 

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.


"ulfb" wrote:

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



"Joel" wrote:

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

"ulfb" wrote:

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