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

Paul Wrote:

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


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=535091