I have say 200 rows in sheet 1.

I want to copy row 1 sheet 1 to row 1 of sheet 2
row 2 of sheet 1 to row 10 of sheet 2
row 3 to row 19
row 4 to row 28

ROW i gets copied to ROW 9*(i-1) + 1

Thanks for any help.

It can be done with a very small macro:

Sub Macro1()
Dim r1, r2 As Range
For i = 1 To 200
Set r1 = Sheets("Sheet1").Rows(i)
Set r2 = Sheets("Sheet2").Rows(9 * (i - 1) + 1)
r1.Copy r2
Next
End Sub



--
Gary's Student


" wrote:

I have say 200 rows in sheet 1.

I want to copy row 1 sheet 1 to row 1 of sheet 2
row 2 of sheet 1 to row 10 of sheet 2
row 3 to row 19
row 4 to row 28

ROW i gets copied to ROW 9*(i-1) + 1

Thanks for any help.

In sheet2, type this in cell A1

=IF(ISBLANK(Sheet1!A1),"",IF(MOD(ROW(Sheet1!A1),9) = 1, Sheet1!A1,
""))

Drag the fill handle right and down as much as you need.

Now you have copied those cells you wanted, but to formalise the
values, you just copy the cells on sheet2, and then paste special back
on the selected area, values.


--
KellTainer
------------------------------------------------------------------------
KellTainer's Profile: http://www.excelforum.com/member.php...o&userid=34322
View this thread: http://www.excelforum.com/showthread...hreadid=550710

Gary''s Student wrote:
It can be done with a very small macro:

Sub Macro1()
Dim r1, r2 As Range
For i = 1 To 200
Set r1 = Sheets("Sheet1").Rows(i)
Set r2 = Sheets("Sheet2").Rows(9 * (i - 1) + 1)
r1.Copy r2
Next
End Sub



--
Gary's Student



worked like a charm.

Thank you very much.

" wrote:

I have say 200 rows in sheet 1.

I want to copy row 1 sheet 1 to row 1 of sheet 2
row 2 of sheet 1 to row 10 of sheet 2
row 3 to row 19
row 4 to row 28

ROW i gets copied to ROW 9*(i-1) + 1

Thanks for any help.

KellTainer wrote:
In sheet2, type this in cell A1

=IF(ISBLANK(Sheet1!A1),"",IF(MOD(ROW(Sheet1!A1),9) = 1, Sheet1!A1,
""))

Drag the fill handle right and down as much as you need.

Now you have copied those cells you wanted, but to formalise the
values, you just copy the cells on sheet2, and then paste special back
on the selected area, values.


--
KellTainer


The macro worked , but I can' t get this method to work. It does do
something but not what I want.
------------------------------------------------------------------------
KellTainer's Profile: http://www.excelforum.com/member.php...o&userid=34322
View this thread: http://www.excelforum.com/showthread...hreadid=550710