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

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

Your company has the help files locked out? That seems to me like a crazy
thing for them to have done. Here is a link to the online help files...

http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

Not as convenient as being able to hit F1, but it should be of some help to
you.

--
Rick (MVP - Excel)


"daisy2008" wrote in message
...
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

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

Might be easier to use
datafilterautofilterfilter on the column with the months for the
monthcopy/paste

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"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

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

Yes, it is crazy but what can you do. Thank you so much for the link that
will come in handy.

Do you have any ideas why this code is not working? I'm just learning. Per
Jessen stated I needed a next statement to continue the loop. So right if I
take that line out it will copy the first row it finds. I need it to copy
all of the rows that has that value. Any ideas?

Daisy :)

"Rick Rothstein" wrote:

Your company has the help files locked out? That seems to me like a crazy
thing for them to have done. Here is a link to the online help files...

http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

Not as convenient as being able to hit F1, but it should be of some help to
you.

--
Rick (MVP - Excel)


"daisy2008" wrote in message
...
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

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

Hi Don,

No that will not work for me. I have users that know nothing about excel
and the easier I can make it on them the better but thank you.

Daisy :)

"Don Guillett" wrote:

Might be easier to use
datafilterautofilterfilter on the column with the months for the
monthcopy/paste

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"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

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

I'm sorry, The month name.

In sheet 1 were the data is we will enter the month when the order is
received in column C. Later on we will run a report that will only pull up
orders received in lets say november or october so we can see if we are in
budget for that month. Does that help a little more?
Daisy :)


"Rick Rothstein" wrote:

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

Then change this statement...

If Month(Cells(R.Row, "C")) = Month(ActiveCell.Value) Then

to this if the month name is fully spelled out in your ActiveCell...

If Format(Cells(R.Row, "C"), "mmmm") = ActiveCell.Value Then

or to this if the month name is a 3-character abbreviation...

If Format(Cells(R.Row, "C"), "mmm") = ActiveCell.Value Then

--
Rick (MVP - Excel)


"daisy2008" wrote in message
...
I'm sorry, The month name.

In sheet 1 were the data is we will enter the month when the order is
received in column C. Later on we will run a report that will only pull
up
orders received in lets say november or october so we can see if we are in
budget for that month. Does that help a little more?
Daisy :)


"Rick Rothstein" wrote:

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

is it just me???? :(

now the error code says run-tim error 91
object variable or with block variable not set then highlights statement

Combo.Copy Worksheets("Sheet2").Range("A3")

Yes that is the name of worksheet 2. Rick, what am I doing? Do you need
more info?

Daisy :)
"Rick Rothstein" wrote:

Then change this statement...

If Month(Cells(R.Row, "C")) = Month(ActiveCell.Value) Then

to this if the month name is fully spelled out in your ActiveCell...

If Format(Cells(R.Row, "C"), "mmmm") = ActiveCell.Value Then

or to this if the month name is a 3-character abbreviation...

If Format(Cells(R.Row, "C"), "mmm") = ActiveCell.Value Then

--
Rick (MVP - Excel)


"daisy2008" wrote in message
...
I'm sorry, The month name.

In sheet 1 were the data is we will enter the month when the order is
received in column C. Later on we will run a report that will only pull
up
orders received in lets say november or october so we can see if we are in
budget for that month. Does that help a little more?
Daisy :)


"Rick Rothstein" wrote:

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

Let's try specifying all the sheet references and see if that makes a
difference...

Sub CopyRows()
Dim R As Range
Dim Combo As Range
For Each R In Worksheets("Sheet1").UsedRange.Rows
If Format(Worksheets("Sheet1").Cells(R.Row, "C"), _
"mmmm") = 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

Remember, copy/paste the above, don't try to type it in.

--
Rick (MVP - Excel)


"daisy2008" wrote in message
...
is it just me???? :(

now the error code says run-tim error 91
object variable or with block variable not set then highlights statement

Combo.Copy Worksheets("Sheet2").Range("A3")

Yes that is the name of worksheet 2. Rick, what am I doing? Do you need
more info?

Daisy :)
"Rick Rothstein" wrote:

Then change this statement...

If Month(Cells(R.Row, "C")) = Month(ActiveCell.Value) Then

to this if the month name is fully spelled out in your ActiveCell...

If Format(Cells(R.Row, "C"), "mmmm") = ActiveCell.Value Then

or to this if the month name is a 3-character abbreviation...

If Format(Cells(R.Row, "C"), "mmm") = ActiveCell.Value Then

--
Rick (MVP - Excel)


"daisy2008" wrote in message
...
I'm sorry, The month name.

In sheet 1 were the data is we will enter the month when the order is
received in column C. Later on we will run a report that will only
pull
up
orders received in lets say november or october so we can see if we are
in
budget for that month. Does that help a little more?
Daisy :)


"Rick Rothstein" wrote:

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

YES, it worked. Thank you so much.

new issue - now if lets say october has 5 orders and november had 3 it is
still showing the 2 october orders anyway to clean that up?

sorry to be such a pain

Daisy :)

"Rick Rothstein" wrote:

Let's try specifying all the sheet references and see if that makes a
difference...

Sub CopyRows()
Dim R As Range
Dim Combo As Range
For Each R In Worksheets("Sheet1").UsedRange.Rows
If Format(Worksheets("Sheet1").Cells(R.Row, "C"), _
"mmmm") = 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

Remember, copy/paste the above, don't try to type it in.

--
Rick (MVP - Excel)


"daisy2008" wrote in message
...
is it just me???? :(

now the error code says run-tim error 91
object variable or with block variable not set then highlights statement

Combo.Copy Worksheets("Sheet2").Range("A3")

Yes that is the name of worksheet 2. Rick, what am I doing? Do you need
more info?

Daisy :)
"Rick Rothstein" wrote:

Then change this statement...

If Month(Cells(R.Row, "C")) = Month(ActiveCell.Value) Then

to this if the month name is fully spelled out in your ActiveCell...

If Format(Cells(R.Row, "C"), "mmmm") = ActiveCell.Value Then

or to this if the month name is a 3-character abbreviation...

If Format(Cells(R.Row, "C"), "mmm") = ActiveCell.Value Then

--
Rick (MVP - Excel)


"daisy2008" wrote in message
...
I'm sorry, The month name.

In sheet 1 were the data is we will enter the month when the order is
received in column C. Later on we will run a report that will only
pull
up
orders received in lets say november or october so we can see if we are
in
budget for that month. Does that help a little more?
Daisy :)


"Rick Rothstein" wrote:

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

Assuming there is **no** other data on Sheet2 that you want or need to
preserve, try this...

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

--
Rick (MVP - Excel)


"daisy2008" wrote in message
...
YES, it worked. Thank you so much.

new issue - now if lets say october has 5 orders and november had 3 it is
still showing the 2 october orders anyway to clean that up?

sorry to be such a pain

Daisy :)

"Rick Rothstein" wrote:

Let's try specifying all the sheet references and see if that makes a
difference...

Sub CopyRows()
Dim R As Range
Dim Combo As Range
For Each R In Worksheets("Sheet1").UsedRange.Rows
If Format(Worksheets("Sheet1").Cells(R.Row, "C"), _
"mmmm") = 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

Remember, copy/paste the above, don't try to type it in.

--
Rick (MVP - Excel)


"daisy2008" wrote in message
...
is it just me???? :(

now the error code says run-tim error 91
object variable or with block variable not set then highlights
statement

Combo.Copy Worksheets("Sheet2").Range("A3")

Yes that is the name of worksheet 2. Rick, what am I doing? Do you
need
more info?

Daisy :)
"Rick Rothstein" wrote:

Then change this statement...

If Month(Cells(R.Row, "C")) = Month(ActiveCell.Value) Then

to this if the month name is fully spelled out in your ActiveCell...

If Format(Cells(R.Row, "C"), "mmmm") = ActiveCell.Value Then

or to this if the month name is a 3-character abbreviation...

If Format(Cells(R.Row, "C"), "mmm") = ActiveCell.Value Then

--
Rick (MVP - Excel)


"daisy2008" wrote in message
...
I'm sorry, The month name.

In sheet 1 were the data is we will enter the month when the order
is
received in column C. Later on we will run a report that will only
pull
up
orders received in lets say november or october so we can see if we
are
in
budget for that month. Does that help a little more?
Daisy :)


"Rick Rothstein" wrote:

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

Thank you so much for your time, Rick.

Daisy :)

"Rick Rothstein" wrote:

Assuming there is **no** other data on Sheet2 that you want or need to
preserve, try this...

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

--
Rick (MVP - Excel)


"daisy2008" wrote in message
...
YES, it worked. Thank you so much.

new issue - now if lets say october has 5 orders and november had 3 it is
still showing the 2 october orders anyway to clean that up?

sorry to be such a pain

Daisy :)

"Rick Rothstein" wrote:

Let's try specifying all the sheet references and see if that makes a
difference...

Sub CopyRows()
Dim R As Range
Dim Combo As Range
For Each R In Worksheets("Sheet1").UsedRange.Rows
If Format(Worksheets("Sheet1").Cells(R.Row, "C"), _
"mmmm") = 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

Remember, copy/paste the above, don't try to type it in.

--
Rick (MVP - Excel)


"daisy2008" wrote in message
...
is it just me???? :(

now the error code says run-tim error 91
object variable or with block variable not set then highlights
statement

Combo.Copy Worksheets("Sheet2").Range("A3")

Yes that is the name of worksheet 2. Rick, what am I doing? Do you
need
more info?

Daisy :)
"Rick Rothstein" wrote:

Then change this statement...

If Month(Cells(R.Row, "C")) = Month(ActiveCell.Value) Then

to this if the month name is fully spelled out in your ActiveCell...

If Format(Cells(R.Row, "C"), "mmmm") = ActiveCell.Value Then

or to this if the month name is a 3-character abbreviation...

If Format(Cells(R.Row, "C"), "mmm") = ActiveCell.Value Then

--
Rick (MVP - Excel)


"daisy2008" wrote in message
...
I'm sorry, The month name.

In sheet 1 were the data is we will enter the month when the order
is
received in column C. Later on we will run a report that will only
pull
up
orders received in lets say november or october so we can see if we
are
in
budget for that month. Does that help a little more?
Daisy :)


"Rick Rothstein" wrote:

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