copy and paste a row

D

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
 
P

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
 
D

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 :)
 
D

Don Guillett

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

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
 
D

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 :)
 
D

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?
 
D

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 :)
 
R

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?
 
D

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 :)
 
R

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
 
D

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 :)
 
R

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.
 
D

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 :)
 
R

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
 
D

daisy2008

Thank you so much for your time, Rick.

Daisy :)

Rick Rothstein said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

ID Number Col A 3
Cut instead of Copy 7
Resize to last entry in row and copy to sheet 1 3
How do I get this macro to start the search in the second row 2
Test variable range for 'Delete'; then delete the row 2
Wrong Row Selected 11
find multiple values code tweak 5
Clear a "moving" range offset from Target 31

Top