can this be done simply ?

[[email protected]]

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.

[Gary''s Student]

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


"[email protected]" 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]

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.

[[email protected]]

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.

> "[email protected]" 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.
> >
> >

[[email protected]]

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