Linked Header Problem - simple one
 

Here is a simple one:

I am attempting to populate a header with a cell from a linked
worksheet.

When I put the contents of the linked cell into a cell, i get the
String Value, in this case "Go Nuggets"

Range("A1").Value = "='C:\Documents and Settings\mwelty\Desktop
\Headers and Footers\[Header_Footer_Template.xls]Sheet1'!R2C1"

However when I try to do the same thing for the Headers and Footers,
it places the entire path ( "='C:\Doc...R2C1") as the header and
doesn't grab the value.

ActiveSheet.PageSetup.RightHeader = "='C:\Documents and Settings\mwelty
\Desktop\Headers and Footers\[Header_Footer_Template.xls]Sheet1'!R2C1"

What am I doing wrong. I have tried manipulating the quotes ect. but
no luck. Thanks to all who answer.