Getting Sheet name from Full Range in VBA
 

On sheet1, cell B3 I have a cell that as a result of a formula has the text:

sheet2!$A$4:$B$6

in my VBA Macro I store the text from Sheet1 into a string

Dim myRange as String
myRange = Sheets("sheet1").Range("$B$3")

The sheet reference on sheet1 could change to be another sheet (instead of
"sheet2" it could be "newSheet" or "lastPage" etc.), thus I want to know what
sheet is referenced in myRange, so that I can change an unrelated cell on
that sheet. Is there a way to do that?

Paul

Getting Sheet name from Full Range in VBA
 

Dim MySheet as String
MySheet = Left(MyRange,InStr(1,MyRange,"!")-1)

HTH

Die_Another_Day
Paul wrote:
On sheet1, cell B3 I have a cell that as a result of a formula has the text:

sheet2!$A$4:$B$6

in my VBA Macro I store the text from Sheet1 into a string

Dim myRange as String
myRange = Sheets("sheet1").Range("$B$3")

The sheet reference on sheet1 could change to be another sheet (instead of
"sheet2" it could be "newSheet" or "lastPage" etc.), thus I want to know what
sheet is referenced in myRange, so that I can change an unrelated cell on
that sheet. Is there a way to do that?

Paul