Example.

Create new workbook with worksheets named "Sheet1" and "Sheet2".

Fill column A on Sheet1 with some data.

Put the next formula into A1 on Sheet2:

`=INDIRECT("Sheet1!R"&INT((ROW()+7)/8)&"C1",FALSE)`

Drag thes formula down.

The values from Sheet1 will be copied onto Sheet1 by blocks 8 copies in each (i.e. A1 will be copied into A1:A8, A2 into A9:A16 and so on).

To adjust the position in any particular case you need to adjust the constant values:

`+7`

, which defines the final offset

`8`

, which defines the block size

`1`

, which defines the source column

You may use the same technique if you need to create horizontal blocks - in such case you must perform calculations in column part of the address (replace `"C1"`

with some `"C"&INT((COLUMN()+const1)/const2)`

and use constant row part). Or you may create rectangle blocks using formulas in both address parts.

`Sheet1!`

constant sets source worksheet. While copying in the same worksheet it can be skipped.

`(ROW()+const)/8`

and`INDIRECT()`

. This is draggable. – Akina Oct 7 at 8:25