Copy the Row if the Criteria matches

[Rajkumar_h]

Hello,

I was in a middle of a creating a macro and got stuck in the below scenario.

There are two sheets namely "Data Sh" and "Summary Sh". In the Data sh we need to match the Criteria "NO" in the Col H and copy the same row data but only four col data (i.e. C4:F4) and paste it in the summary sh.

I had used the Offset function but it was giving an error. I'm attaching the Example Scenario file for your reference.

Regards,
Rajkumar H

[jindon]

Try

Please Login or Register  to view this content.

[leelnich]

Very nice, Jindon! But a question, what's the "(2)"?:

Please Login or Register  to view this content.

It appears to be a parameter for the paste, but doesn't match any XlPasteType constant (which usually only apply to PasteSpecial anyway).

[jindon]

It's a Cells(2,1) and second protocol is optional.

[leelnich]

Yeah, that makes sense-you needed to move down a row- but I've never seen the Cells Keyword skipped before.

[jindon]

You can find in my posts, like
https://www.excelforum.com/excel-pro...ml#post3321278

Cells(1,1) is the default, so
r(,2) means r.Cells(1,2) = r.Offset(,1)

[Rajkumar_h]

Awesome Jindon! thanks for your help on this. I had used "For each loop" for this scenario but the code which you have used is amazing, very simple and haven't seen anyone written in such a different manner.
Even I would like to write code in such a way, could you help me with the required study material.

Thanks & Regards,
Rajkumar