Excel Macro to select and cut multiple rows to a new worksheet

[sw3nyce]

I run a report every week and would like to cut the rows for a particular date range into a new worksheet (i.e. all rows with transactions between the 16th of one month to the 15th of the following month).

I'd like to cut all rows between 07/16 and 08/15 and past them in a new worksheet called "Aug" And cut all rows between 08/16 and 09/15 and past them in a new workseet called "Sept"

Example data
Column A - Column B - Column C
Post Date - Name - Vendor
07/25/10 - Wilson - Starbucks
08/05/10 - Wilson - American Airlines
08/14/10 - Guang - Apple Store
08/15/10 - McGee - Staples
08/17/10 - Curry - AT&T
09/02/10 - Johnson - Exxon
09/11/10 - Simms - Old Navy

Below is the VBA code I'm using but i'm getting an error on the red line......ANY SUGGESTIONS??????


Dim workingrow As Range

For Each workingrow In Sheets("Main").Rows

If (workingrow.Cells(1, 1).Value >= DateValue("7/16/2010")) And _
(workingrow.Cells(1, 1).Value <= DateValue("8/15/2010")) Then
workingrow.Copy (Sheets("Aug").Rows(1).End(xlDown).Offset(1, 0))

End If
Next workingrow

 

[seenfresh]

Try the following:

Sub Test_()

On Error GoTo ErrHandler:

Dim mywb As Workbook
Dim myws As Worksheet, myws1
Dim iRow As Long
Dim mydata As Range

'set object references
Set mywb = ActiveWorkbook
Set myws = mywb.Worksheets("Main")
Set myws1 = mywb.Worksheets("Aug")

With myws

wslstrw = .Range("A" & .Rows.Count).End(xlUp).Row

For iRow = 2 To wslstrw

myvalue = DateValue(.Cells(iRow, "A").Value)

If myvalue >= DateValue("7/16/2010") And myvalue <= DateValue("8/15/2010") Then

Set mydata = .Rows(iRow)
    mydata.Copy

    With myws1
      ws1lstrw = .Range("A" & Rows.Count).End(xlUp).Row + 1
        .Paste Destination:=.Range("A" & ws1lstrw)
    End With
End If

Set mydata = Nothing


Next iRow

End With

Exit Sub

ErrHandler:
    MsgBox Err.Number & " " & Err.Description

End Sub