Sorry, I still don't get it.
Perhaps my description needs enhancement. I have many sheets, one is MAIN,
another is INFO, another is THIRDONE, and then many others. When I am in the
MAIN sheet, I would like to fire a macro that will take me to whatever sheets
name is listed in the INFO sheet cell L1.....for this example it would be
THIRDONE.

Vaya con Dios,
Chuck, CABGx3



"Brotha Lee" wrote:

Hi CLR,

You should separate the sheet name from the range, so Info!L1 wont work.

You should use to separate cell for the sheetname Info and cell L1

HTH


"CLR" wrote:

Thanks, but I can't seem to get it to work.......I'm using XL97, maybe thats
why?

When I use this code, the messagebox comes up with the right name, but the
next line won't work........gives "Subscript out of range" error.

Sub sht_select()
Dim mySheet As String
mySheet = Range("info!L1").Value
MsgBox mySheet
Sheets(mySheet).Select
End Sub

Vaya con Dios,
Chuck, CABGx3



"Brotha Lee" wrote:

CLR,

Try the following.

Let's say cell A1 in your sheet contains the sheetname and cell A2 the cell
reference. You can use the following then

sub sht_select()
dim mySheet as string
dim myRange as string

mySheet = activesheet.range("A1").value
myRange = activesheet.range("A2").value

sheets(mySheet).select
activesheet.range(myRange).select

end sub

"CLR" wrote:

Hi All.......
Right now I am using the following line to select a sheet.

Sheets("MySheetName").Select

I would rather like to select the sheet without having to hardcode the name
in the macro, that is, I would like to specify a SheetName and CellAddress
that would have the sheet name entered in it.

Any help would be much appreciated.

Vaya con Dios,
Chuck, CABGx3