Copy Rows to a new worksheet with unmatching cells

Hi
I am trying to work out how to copy data that is in rows in Sheet 1 to a new worksheet Sheet2 that contains 8 row templates.

EG:
Sheet 1
Cols A - I
Rows 1...Data
copy to Sheet 2
into CELL B2,D2,J2,J3,B4,J4,J5,J6,J7

Sheet 1
Cols A - I
Rows 2...Data
copy to Sheet 2
into CELL B10,D10,J10,J11,B12,J12,J13,J14,J15

Sheet 1
Cols A - I
Rows 3...Data
copy to Sheet 2
into CELL B18,D18,J18,J19,B20,J20,J21,J22,J24

and so on and on in increments of 8

Any help please
Bern

Have you tried writing equations like :

If I understand what you want to do then in :

Sheet2 Cell B2 the equation would be :

='Sheet1'!A1

Sheet2 Cell D2 the equation would be :

='Sheet1'!B1

Sheet2 Cell J2 the equation would be :

='Sheet1'!C1

Sheet2 Cell J3 the equation would be :

='Sheet1'!D1

etc...

Once you have a got enough for a pattern to be recognised by Excel, you can copy the lot down/across as you need to.

You might be able to get what you want using the offset() function.

Set Sheet2!B2 to =OFFSET(Sheet1!A$1,(ROW()-2)/8,0)
Set Sheet2!D2 to =OFFSET(Sheet1!B$1,(ROW()-2)/8,0)
Set Sheet2!J2 to =OFFSET(Sheet1!C$1,(ROW()-2)/8,0)
Set Sheet2!J3 to =OFFSET(Sheet1!D$1,(ROW()-3)/8,0)
Set Sheet2!D3 to =OFFSET(Sheet1!E$1,(ROW()-3)/8,0)
Set Sheet2!J4 to =OFFSET(Sheet1!F$1,(ROW()-4)/8,0)
Set Sheet2!J5 to =OFFSET(Sheet1!G$1,(ROW()-5)/8,0)
Set Sheet2!J6 to =OFFSET(Sheet1!H$1,(ROW()-6)/8,0)
Set Sheet2!J7 to =OFFSET(Sheet1!I$1,(ROW()-7)/8,0)

Can you see the pattern?

If you copy these cells and paste them onto Sheet2!B10 etc.
you will find that the addresses automatically update to point to Row 2 on sheet1 [due to the (Row()-n)/8 offset].

Mark.

Hi
Thanks Dave, Peter, and Mark. All good suggestions but unfortunately I have tried to record a macro but it did not work or better still I could not do it right. Problem being I cannot copy into 300 plus cells, same with Peter's suggestion it works but it took me 30 mins to get to copy cell 30! Which is not what I need.
Mark your code worked for the first 24 cells but then it would only replicate by 8 x sheet 1.
I need row 2 in sheet1 copying to Cells B2,D2,J2,J3,B4,J4,J5,J6,J7 in sheet2
then row 3 in sheet1 copying to Cells B10,D10,J10,J11,B12,J12,J13,J14,J15 in sheet 2
I need to do this for at least 300 rows in sheet 1 going to every cell as above in incriminates of 8 in Sheet2.

Bern

I think the following macro will do what you want.

I tried to make it somewhat customizable. (Hopefully it makes sense to you.)

You will need to make 2 changes in order for it to work: (those two lines are highlighted in bold)

Your source sheet. Instead of "Sheet1", put the name of your source worksheet in there, ie:
Set Source = Worksheets("Sheet1") -> Set Source = Worksheets("Raw Data")

Your destination sheet. Instead of "Sheet2", put the name of your destination worksheet in there, ie:
Set Dest = Worksheets("Sheet2") -> Set Dest = Worksheets("Summary")

Other than that, it should work to your specs.

Please Login or Register  to view this content.

Scott

Hi Scott
Absolutely Awesome!
I fully realize the amount of coding that went into your macro and I am in total awe. The script worked beautifully and my hat goes off to you.
Thank you so much

Bern