Getting Worksheet name in Reference Style

[daneclab]

I want to get the address of a cell I am referencing. If I use
Address(external=:True) then I get the workbook name included in the
reference. If I use AddressLocal I get just the row/column reference.
How can I get the address where it looks like =TRAVEL!$A$10, where
TRAVEL is the name of the worksheet where the cell resides?

 

[PCLIVE]

This should give you a good start.

wsName = ActiveSheet.Name
cellAdress = ActiveCell.Address

Where is this to be displayed?

 

[Guest]

If you are building a formula, you probably don't need to worry about it.

Excel will clean it up when you enter it as a formula.


just to demo from the immediate window:

Worksheets("Sheet1").range("C2").Formula = "=" & _
Worksheets("Sheet2").Range("B9").Address(0,0,xla1,true)
? worksheets("Sheet1").Range("C2").formula
=Sheet2!B9