Copy entire row if value equals, paste in different range.

wdrvx

New Member
Joined
Oct 4, 2016
Messages
17
Hey Guys,

I'm a VBA beginner and I've spent last 6 hours trying to make my macro copy content of one worksheet into another if cell value equals certain string. It works well when the data is pasted in the exact same columns (A:Q) but it doesn't when I try to paste the data into a different range (C:S).

Excel tells me that it cannot be pasted because copy and paste area are of different shape and size which is untrue. The same operation is completed without a problem when done manually or using simple destination copy without a condition.

Below is my code:

Code:
Sub copydata()    
    Dim value As Range
    Dim start As Integer
    Dim srcSh As Worksheet
    Dim trgSh As Worksheet


    Set srcSh = ActiveWorkbook.Worksheets("Data")
    Set trgSh = ActiveWorkbook.Worksheets("Ready")


    start = 12


    For Each value In srcSh.Range("A2:Q500")
        If value = "Reviewed" Then
           srcSh.Rows(value.Row).copy trgSh.Rows(start).Range("C12")
           start = start + 1
        End If
    Next value
End Sub

Thanks in advance for all your help.
 
Last edited:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
It is because if this line here:
Code:
srcSh.Rows(value.Row).copy trgSh.Rows(start).Range("C12")
You are copying the whole row, meaning every single possible column in that row.
If you do that, the only way it will work if if you paste it into column A of your destination sheet. If you choose any other column, it will not work, as it would go off the page.

Actually, I am not quite sure I understand your destination range reference. It looks odd and possibly incorrect:
trgSh.Rows(start).Range("C12")
You only would have one range reference, either Rows or Range, but not both.
What exactly are you trying to do there?
 
Upvote 0
Hi Joe,

Thanks for the explanation, I did not realize it works in such a way :)

What I'm trying to achieve here is that whenever an item has been reviewed (value in column C is equal) entire row is being copied into another sheet starting from C12 as both A & B on this sheet are being used for something else. Then after everything is copied I'd want to sort this using custom order, but this is something I'm gonna try myself and see if I can manage with that ;)

I like to solve things on my own as it's a good learning opportunity, but I really got stuck here and couldn't find any solution on the net so decided to ask for your assistance.

Actually, I am not quite sure I understand your destination range reference. It looks odd and possibly incorrect:
trgSh.Rows(start).Range("C12")
You only would have one range reference, either Rows or Range, but not both.
I wasn't sure about this one either as it looked weird to me. Would you be able to show me a good example of this?

Thanks a lot!
 
Upvote 0
OK. A few tips.

Don't use works that are the names of existing Excel functions or properties as variable names. It can lead to errors and unexpected results. So I changed your "value" variable to simply "val".

When pasting to a range, you only need to designate the first cell to paste to (not the entire range).

When working with dynamic column references, you can use either Range or Cells(row,column), i.e. the following all refer to cell D10:
Range("D10")
Cells(10,"D")
Cells(10,4)


The cells one is often easier to use when trying to select multiple cells over a range, and/or your have variables for both rows and columns.
I used a mixture of both in the code below, so you can see how it works.

Assuming that the word "Reviewed" is appearing in column A, this code should do what you want (if not, just change A2:A500 to the column where this word appears).
Code:
Sub copydata()
    
    Dim val As Range
    Dim start As Integer
    Dim srcSh As Worksheet
    Dim trgSh As Worksheet

    Set srcSh = ActiveWorkbook.Worksheets("Data")
    Set trgSh = ActiveWorkbook.Worksheets("Ready")

    start = 12

    srcSh.Activate
    For Each val In srcSh.Range("A2:A500")
        If val = "Reviewed" Then
           srcSh.Range(Cells(val.Row, "A"), Cells(val.Row, "Q")).Copy trgSh.Range("C" & start)
           start = start + 1
        End If
    Next val

End Sub
 
Last edited:
Upvote 0
Excellent job, thank you so much Joe. Works perfectly. I'll make sure I stick to your tips :) Ok, I guess it's time to move on to sorting thing now. I'll see how I get on and let's hope I won't have to moan for help again

Thanks again, have a great weekend! :)
 
Upvote 0
You are welcome.

If you run into issues with your sorting, feel free to post a new question (since it would be a new question, best to post it in a new thread).
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top