Get SheetName from cell

[CLR]

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

 

[Brotha Lee]

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]

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]

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

 

[Vergel Adriano]

Chuck,

try changing this line:

mySheet = Range("info!L1").Value

to become like this

mySheet = Sheets("info").Range("L1").Value

 

[CLR]

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

 

[CLR]

That did the trick!...........thanks most kindly

Vaya con Dios,
Chuck, CABGx3

 

[Tom Ogilvy]

Just for information, in a general module, either one will work:

demo'd from the immediate window
? Range("info!L1").Value
Chuck


? Sheets("info").Range("L1").Value
Chuck

This is true in xl97 as well.

In a sheet module you would have a problem.

the code you show shouldn't be in a sheet module since it isn't related to
an event.

 

[CLR]

Thanks for the info Tom.........the code is in a General Module, and works
now with Vergel's suggestion. I'm running in to problems tho, because the
code is so convoluted.........there are 29 uses of this sheetname and now I'm
having to hardcode the sheetname in, whereas Vergel's suggestion helps on
some of them, .....I'm still having some trouble and am looking in to a macro
that will go in and do a "Find and Replace" on all my other macros for this
hardcoded sheetname.........I've got one suggestion to look at Chip's site
but have not found what I need there yet. What I want to do is to scan all
my General Modules, or everything, and replace _GB with _CR, whether it occur
in a remmed out comment or live code.........right now I'm having to do that
by hand...........still looking, but user wants so many other features in the
program that I been spending time on them......

Vaya con Dios,
Chuck, CABGx3

 

[KymY]

Where's the "=Sheet" function I used to use, in Lotus 123 ????

Kym Yeoward

 

[NickHK]

Still there, I would imagine.

If you mean "how can achieve the functionality of the Lotus function
"=Sheet" in Excel", it would depend on what it does.

Maybe parse the info from:
=CELL("filename",A1)

NickHK

 

[KymY]

Hint: The Filename string shows the sheet name after the worksheet name,
which ends with a "]"

Have since found the following text , to show the current sheet name in a
cell:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)