Sheet Name in .address

Topic

New Reply

I need to get the full address (ie including the sheet name) back from a selected range.

The following …

range(“NamedRange”).address

returns $A$4 (if NamedRange refers to SheetName!$A$4

BUT BUT BUT how do I get it to return

‘SheetName!$A$4???

Can someone please help (and if it turns out to be something like “.fulladdress” I am going to scream.)

Cheers

Tim

Reply | Quote

Replies

Dont scream but what I think you are looking for is ActiveSheet.Name + range(“NamedRange”).address

Hope that works, and perhaps somebody will post a more elegant solution

Andrew

Reply | Quote

Try:

Dim sW As String
    sW = Names("sheet2!Name").RefersTo

Reply | Quote

Of course!

I was using ‘refersto’ to set the range name via the macro, I just didn’t think that it would be a readable property as well.

Now, all I need to use it in ‘range().value’ is strip the “=” out – easy.

Thanks all for you help (and the speed!!)

Reply | Quote

Actually the reply was a bit slow. I had just created a VBA module to test that when they delivered my Sushi for lunch. Your answer took second place.

Glad I could help.

Reply | Quote

This simple line of code should give you the name of the worksheet of the “NamedRange”:
Range(“NamedRange”).Parent.Name

Reply | Quote