Can I have Paste Link copy formating?

[CarloPel]

I have a macro (Excel 2003) that paste links into another worksheet, however,
the formating of the font (size and color) does not come across.
Sheets("Highlights").Select
Range("$AR$2:$AR$2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Summary").Select
Range("J2").Select
ActiveSheet.Paste Link:=True

I noticed that if I do this manually, it works( color comes across). Is
there a special form of the paste link command to use?

 

[Dave Peterson]

Pasting the link essentially builds a formula that points back to that original
cell/range.

You could record a macro when you copy the range and edit|paste special|formats
and have your code.

Be aware that this formatting won't change if the formatting of the original
range changes.

 

[CarloPel]

OK, but it doesn't work:

Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Macro fails with error code 1004 "PasteSpecial method of Range class failed"
if I insert this into the code below.

 

[Dave Peterson]

Dim RngToCopy As Range
Dim DestCell As Range

With Worksheets("Highlights")
Set RngToCopy = .Range("ar2", .Range("ar2").End(xlDown))
End With

Set DestCell = Worksheets("Summary").Range("J2")

RngToCopy.Copy
Application.Goto DestCell
ActiveSheet.Paste link:=True

RngToCopy.Copy
DestCell.PasteSpecial Paste:=xlPasteFormats