Copy cells

[Brodito]

I am trying to copy cells in a non sequential order:
For Example, I have the following sheet:

In Sheet 1, I have
A B C D
1 A
2 B
3 C

In Sheet 2, I have
A
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9

I am trying to create in Sheet 1 a formula that allows me to have this result:

A B C D
1 A 1 2 3
2 B 4 5 6
3 C 7 8 9

I copy in Sheet 1 (B1), =Sheet 2!A1; then in (C1) =Sheet 2!A2; and in (D1) =Sheet 2!A3... Until now is fine

But when I copy in (B2), I would like to copy =Sheet 2!A4, not A2... How can I copy "NON SEQUENTIAL" cells; or, is it possible to instruct to copy A1 "PLUS" 3 positions below, incrementing 3 positions (not only one)?

Hope I make myself understood... Thanks in advance.

 

[id107]

Not sure how to do it using a formula, but this should work:

Sub test()
 
    Dim c As Range, i As Long
 
    i = 1
 
    For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
        c.Offset(, 1).Value = "=Sheet2!A" & i
        c.Offset(, 2).Value = "=Sheet2!A" & i + 1
        c.Offset(, 3).Value = "=Sheet2!A" & i + 2
        i = i + 3
    Next c
 
End Sub

 

[Brodito]

Sorry but do not know how to use the programming you wrote below. Is that done in the developer mode?

 

[indiantrix]

Put this into B1, copy it across to D1, then copy this row Down to Row 3.
HTH - Larry.
and now, for the formula! =INDIRECT("Sheet2!A"&COLUMN()-1+(ROW()*3)-3)