How to Copy rows which match criteria into a new workbook and format it

I have to copy rows which match a given value into a new workbook. They need to be formatted so that some cells appear in one row, others in the next row like in the example. I am stumped. Can anyone give me any ideas about how to do this? I haven't done any Excel programming. But I know C and a bit of visual basic.

My worksheet:
c1 c2 c3 c4 c5 c6
r1 sam
r2 sam
r3 cat
r4 john
r5 jake


For all c1=sam, these data should be copied as such
c1 c2 c3
r1 sam data from r1.c2 data from r1.c3
r2 data from r1.c4 data from r1.c5
r3 data from r1.c6
r4
r5 sam data from r2.c2 data from r2.c3
r6 data from r2.c4 data from r2.c5
r7 data from r2.c6

I am looking for some hints on how to get started.

Hello Ndunross,

This code copies and formats the data as you described in your post. It does not rename and close the new workbook after it runs though. It copies data starting at cell "A2" on "Sheet1" in the Source workbook (this where the macro code resides) and copies cells "A2:F1" to the Destination workbook (the new one) in 3 lines and adds a blank afterwards. It starts at "A2" in case you have a Headers in row 1 of the worksheet.

Please Login or Register  to view this content.

If you have any questions about the code does or want to add/change something, let me know.

Sincerely,
Leith Ross