Sorry, I left a test code reference in my posted code instead of the
reference you wanted. See if this works for you...

Sub CopyRows()
Dim R As Range
Dim Combo As Range
For Each R In Worksheets("Sheet1").UsedRange.Rows
If Month(Cells(R.Row, "C")) = Month(ActiveCell.Value) Then
If Combo Is Nothing Then
Set Combo = R.EntireRow
Else
Set Combo = Union(Combo, R.EntireRow)
End If
End If
Next
Combo.Copy Worksheets("Sheet2").Range("A3")
End Sub

If this code still doesn't work, then it probably means your ActiveCell
doesn't have a date in it. If that is the case, then what is in the
ActiveCell... the month name or month number?

--
Rick (MVP - Excel)


"daisy2008" wrote in message
...
Yes that is correct the month is the active cell in sheet 3. No there
will
only be one year.

I put that code in and I'm getting a mismatch error now. :( What am I
doing
wrong?

"Rick Rothstein" wrote:

Still not enough detail. Is the ?? the month for the date in the
ActiveCell
(your original code was referencing the ActiveCell)? Are there more than
one
year's worth of data on the sheet (meaning the same month for different
years is possible in your data)? Here is a macro that assumes the answer
was
"yes" to my first question...

Sub CopyRows()
Dim R As Range
Dim Combo As Range
For Each R In Worksheets("Sheet1").UsedRange.Rows
If Month(Cells(R.Row, "C")) = Month(ActiveCell.Value) Then
If Combo Is Nothing Then
Set Combo = R.EntireRow
Else
Set Combo = Union(Combo, R.EntireRow)
End If
End If
Next
Combo.Copy Worksheets("Sheet6").Range("A3")
End Sub

--
Rick (MVP - Excel)


"daisy2008" wrote in message
...
Hello,

I need this to copy all rows that have the month of ?? in colmun c to
be
pasted to sheet 2. Sometimes it might be 3 orders placed and sometimes
it
might be 10 for that month. I only need to see the desired month at a
time.
Does that help?

Daisy :)

"Per Jessen" wrote:

Hi

You have started a loop with the For i=... statement. It needs a
"Next" statement to continue the loop.
But I'm not sure the code is working as desired. If you need further
help describe in words what you desire.

Sub copy_rows()
Dim LastRow As Long, i As Long
Dim wksToSearch As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range

Set wksToSearch = Sheets("Sheet1")
Set rngToSearch = wksToSearch.Columns("C")
LastRow = wksToSearch.Cells(Rows.Count, 3).End(xlUp).Row

For i = LastRow To 1 Step -1
Set rngFound = rngToSearch.Find(What:=ActiveCell, LookIn:=xlValue)
If rngFound Is Nothing Then
MsgBox "No data found"
Else
wksToSearch.Select
rngFound.EntireRow.Copy Sheets("Sheet2").Range("A3")
End If
Next
End Sub

Regards,
Per


On 22 Dec., 17:56, daisy2008
wrote:
I have played with this code but I can not make it work. Can
someone
please
help me fix this? My company has the help files locked out. comes
back as
Compile error: For without Next.

Sub copy_rows()
Dim lastrow As Long, i As Long
Dim wksToSearch As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range

lastrow = Sheets("Sheet1").Cells(Rows.Count, 3).End(xlUp).Row
For i = lastrow To 1 Step -1

Set wksToSearch = Sheets("Sheet1")
Set rngToSearch = wksToSearch.Columns("C")
Set rngFound = rngToSearch.Find(What:=ActiveCell,
LookIn:=xlValue)

If rngFound Is Nothing Then
MsgBox "No data found"

Else
wksToSearch.Select
rngFound.EntireRow.Copy Sheets("Sheet2").Range("A3")
End If
End Sub