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

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

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

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

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

Chuck,

try changing this line:

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

to become like this

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



--
Hope that helps.

Vergel Adriano


"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

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

Vaya con Dios,
Chuck, CABGx3



"Vergel Adriano" wrote:

Chuck,

try changing this line:

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

to become like this

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



--
Hope that helps.

Vergel Adriano


"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

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.

--
Regards,
Tom Ogilvy




"CLR" wrote:

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

Vaya con Dios,
Chuck, CABGx3



"Vergel Adriano" wrote:

Chuck,

try changing this line:

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

to become like this

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



--
Hope that helps.

Vergel Adriano


"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