Copy rows based on cell value

[1100feet]

Hi,
I'm needing some help modding this code.
WS "Activities" has a list of dates in col B
(Inputted as 02/01/2012, 04/01/2012, 01/02/2012 etc.)
(Formated as 02-Jan-2012, 01-Feb-2012 etc.)

WS "MonthReport" is where the rows are being pasted
Currently the code will paste rows based on the search string in MonthReport("B1")
So - If I type 02/01/2012 in B1 it copies all the rows where that is the date.

What I need it to do is be able to search for "January" and then return all the rows where the date is between 01/01/2012 and 31/01/2012.
(Or all of Feb, Mar etc dependant on what is in MonthReport("B1")

Any thoughts?


Private Sub CommandButton1_Click()
Dim LSearchRow As Long
Dim LCopyToRow As Long

'On Error GoTo Err_Execute

'Start search in row 4
LSearchRow = 4

'Start copying data to row 10 in MonthReport (row counter variable)
LCopyToRow = 10
Sheets("Activities").Select
While Len(Range("A" & CStr(LSearchRow)).Value) > 0

'If value in column B = "Mail Box", copy entire row to MonthReport
If Range("B" & CStr(LSearchRow)).Value = Sheets("MonthReport").Range("b1").Value Then

'Select row in Activities to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

'Paste row into MonthReport in next row
Sheets("MonthReport").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

'Move counter to next row
LCopyToRow = LCopyToRow + 1

'Go back to Activities to continue searching
Sheets("Activities").Select

End If

LSearchRow = LSearchRow + 1

Wend

'Position on cell A3
Application.CutCopyMode = False
Sheets("Activities").Select
'Range("A3").Select

MsgBox "All matching data has been copied."

Exit Sub

Err_Execute:
MsgBox "An error occurred."

End Sub

 

[Alpacino]

Can u use an autofilter to select the month then copied it to worksheet?? In a macro.

 

[1100feet]

I guess I could do it that way.
Was hoping not to have to though.

Thanks - I may come back to that solution if need be

 

[1100feet]

Are there any options like this...
On the line
If Range("B" & CStr(LSearchRow)).Value = Sheets("MonthReport").Range("b1").Value Then

why can't I add something like (NumberFormat = "mmm") so it would be

If Range("B" & CStr(LSearchRow)).Value (NumberFormat = "mmmm") = Sheets("MonthReport").Range("b1").Value Then

Now I know this doesn't work.
Are there any simple options like this?

 

[1100feet]

This works

Dim searchMonth As Integer
searchMonth = 1

If Month(Range("B" & CStr(LSearchRow))) = searchMonth Then

Just need to build some "if's" to change searchMonth = * depending on the value of a cell