can this be done simply ?

A

analyst41

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.
 
G

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
 
K

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 th
values, you just copy the cells on sheet2, and then paste special bac
on the selected area, values
 
A

analyst41

Gary''s Student said:
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
Click to expand...

worked like a charm.

Thank you very much.
 
A

analyst41

KellTainer said:
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.
Click to expand...


The macro worked , but I can' t get this method to work. It does do
something but not what I want.
 
Top