Take a look at Address in VBA Help:

One way:

Range("D5").Formula = "=Sheet1!" & _
Range("FirstCell").Address(False, False)


In article ,
wrote:

I am trying to write VBA code to return the cell address on one
worksheet within a workbook to another worksheet. The result will be
in the simple formula, for example "='Sheet1'!B3" and this formula
will be on Sheet 2.

I have a macro that is giving the cell on Sheet1 a range name,
"FirstCell" in this case, because the cell I want is a moving target.
I have another piece of macro code which is the following,
Range("D5").Formula = "='Sheet1!" & Range("FirstCell").Address. This
is doing a great job of putting the formula ='Sheet1'!$B$3 on Sheet 2
in cell D5. The problem is that it is an absolute reference and I
want a relative reference. How can I change this result so I will
have the formula ='Sheet1'!B3.

Thanks, Ken