copy and paste a row

[daisy2008]

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

 

[Per Jessen]

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

 

[Rick Rothstein]

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/library/aa272268(office.11).aspx

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

 

[daisy2008]

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

 

[Don Guillett]

Might be easier to use
data>filter>autofilter>filter on the column with the months for the
month>copy/paste

 

[Rick Rothstein]

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

 

[daisy2008]

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

 

[daisy2008]

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?

 

[daisy2008]

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

 

[Rick Rothstein]

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?

 

[daisy2008]

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]

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

 

[daisy2008]

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]

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.

 

[daisy2008]

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]

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

 

[daisy2008]

Thank you so much for your time, Rick.

Daisy

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