Using a loop what would be the correct way to copy a multiple range, create
a new worksheet and paste it to as a link in another worksheet.

i would like to select ranges d2:h66 and d74:h75 in sheet 1 and paste it as
a link to the new sheet 2 .

then i would like to select the next 2 ranges (i2:j66 and i74:j75) in
worksheet 1 and paste them as a link to worksheet 2 in the next column,

then loop back
selecting ranges d2:h66 and d74:h75 ( this is a constant range used in all
sheets) in sheet 1 and paste it as a link to a new sheet (sheet3) then select
the next multiple range in worksheet1 and paste it to worksheet 3

I would like to do this 28 times

I had tried using union statements

to select and join the ranges with r1 as first range and r2 second range r3
as the union, but I don't know the correct syntax to refer to a range in a
loop. and I could not then copy and paste r3 as a link to the other worksheet.

i tried using
r3.copy
worksheets("sheet2").pastelink=true

but this is so wrong.

please please please help.

thank you in advance