ERROR IN CODE

[Paul]

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

[broro183]

Hi Paul,

Have a look at http://www.j-walk.com/ss/excel/tips/tip36.htm for copying multiple selections.
I haven't tested it but I'm guessing that this could be adapted to paste the links instead of the values by adding
Dim PasteLinkRange As Range
to the top of the macro & replacing the line
"SelAreas(i).Copy PasteRange.Offset(RowOffset, ColOffset)"
with the following lines:
"SelAreas(i).Copy 'PasteRange.Offset(RowOffset, ColOffset)
Set PasteLinkRange = PasteRange.Offset(RowOffset, ColOffset)
PasteLinkRange.Select
ActiveSheet.Paste Link:=True"

Maybe I'm misunderstanding but why don't you select the ranges as d2:j66 & d74:j75 (since the ranges aare side by side) rather than as below?...

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,

John's code should put you on the right track to looping through a list of ranges but if you have any problems just ask.

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...