VBA to extract sheet name
 

I am using Office 2003 on Windows XP.

I have a string that looks like: ='Sheet Name'!R22C7

This string is in a variable, so: sSheet = "='Sheet Name'!R22C7"

The length of the sheet name and the cell address changes each time.

I need, in VBA code, to strip out the SHEET NAME only.
Is it possible to do this in a SINGLE LINE of code?

Could someone please post example code that can do this?
Thanks much in advance

Thanks much in advance.

VBA to extract sheet name
 

MyVariable = ActiveSheet.Name


or
MyVariable = Worksheets(1).Name

--
When you lose your mind, you free your life.


"XP" wrote:

I am using Office 2003 on Windows XP.

I have a string that looks like: ='Sheet Name'!R22C7

This string is in a variable, so: sSheet = "='Sheet Name'!R22C7"

The length of the sheet name and the cell address changes each time.

I need, in VBA code, to strip out the SHEET NAME only.
Is it possible to do this in a SINGLE LINE of code?

Could someone please post example code that can do this?
Thanks much in advance

Thanks much in advance.

VBA to extract sheet name
 

MyVariable = "='Sheet Name'!R22C7"

MySheetName = Mid(MyVariable, InStr(1, MyVariable, "'") + 1,
InStrRev(MyVariable, "'") - InStr(1, MyVariable, "'") - 1)

MsgBox MySheetName




--
When you lose your mind, you free your life.


"XP" wrote:

I am using Office 2003 on Windows XP.

I have a string that looks like: ='Sheet Name'!R22C7

This string is in a variable, so: sSheet = "='Sheet Name'!R22C7"

The length of the sheet name and the cell address changes each time.

I need, in VBA code, to strip out the SHEET NAME only.
Is it possible to do this in a SINGLE LINE of code?

Could someone please post example code that can do this?
Thanks much in advance

Thanks much in advance.