Select sheet based on cell value
 

Hi,

I have 25 sheet named Sheet 1 to Sheet 25.
What i am looking for is..

I need to select the specific sheet whos name is written in sheet1 A1 cell.

For example if Sheet 1 cell A1 = sheet2 and i run the macro it should take
me to sheet 2

and if Sheet 1 cell A1 = sheet14 and i run the macro it should take me to
sheet 14.

So on and so forth.

I had a similar macro but i lost it. please help me..
Thanks a lot
Boss

Select sheet based on cell value
 

I use this where I have a list and double click on the cell with the sheet
name
Right click sheet tabview code insert this

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(WantedSheet) Is Nothing Then
'GetWorkbook ' calls another macro to do that
Else
Application.Goto Sheets(WantedSheet).Range("a4")
End If
Application.DisplayAlerts = True
End Sub

Or, to do it the way you suggest, use a worksheet_CHANGE event restricted to
cell a1






--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Boss" wrote in message
...
Hi,

I have 25 sheet named Sheet 1 to Sheet 25.
What i am looking for is..

I need to select the specific sheet whos name is written in sheet1 A1
cell.

For example if Sheet 1 cell A1 = sheet2 and i run the macro it should take
me to sheet 2

and if Sheet 1 cell A1 = sheet14 and i run the macro it should take me to
sheet 14.

So on and so forth.

I had a similar macro but i lost it. please help me..
Thanks a lot
Boss

Select sheet based on cell value
 

This was great.. thansk for it.

Well i just found the macro which i lost. take a look on the same.


Sub SelectWorkheet()
Dim strWsName As String

strWsName = Sheet1.Range("a1")
Sheets(strWsName).Select
End Sub

Tahnks
Boss
"Don Guillett" wrote:


I use this where I have a list and double click on the cell with the sheet
name
Right click sheet tabview code insert this

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(WantedSheet) Is Nothing Then
'GetWorkbook ' calls another macro to do that
Else
Application.Goto Sheets(WantedSheet).Range("a4")
End If
Application.DisplayAlerts = True
End Sub

Or, to do it the way you suggest, use a worksheet_CHANGE event restricted to
cell a1






--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Boss" wrote in message
...
Hi,

I have 25 sheet named Sheet 1 to Sheet 25.
What i am looking for is..

I need to select the specific sheet whos name is written in sheet1 A1
cell.

For example if Sheet 1 cell A1 = sheet2 and i run the macro it should take
me to sheet 2

and if Sheet 1 cell A1 = sheet14 and i run the macro it should take me to
sheet 14.

So on and so forth.

I had a similar macro but i lost it. please help me..
Thanks a lot
Boss

Select sheet based on cell value
 

Worksheet_change version. Simply type in the name of the sheet. If it's
there, you go there

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$1" Then Exit Sub
Dim WantedSheet As String
WantedSheet = Trim(Target)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(WantedSheet) Is Nothing Then
'GetWorkbook ' calls another macro to do that
Else
Application.Goto Sheets(WantedSheet).Range("a4")
End If
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Boss" wrote in message
...
This was great.. thansk for it.

Well i just found the macro which i lost. take a look on the same.


Sub SelectWorkheet()
Dim strWsName As String

strWsName = Sheet1.Range("a1")
Sheets(strWsName).Select
End Sub

Tahnks
Boss
"Don Guillett" wrote:


I use this where I have a list and double click on the cell with the
sheet
name
Right click sheet tabview code insert this

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(WantedSheet) Is Nothing Then
'GetWorkbook ' calls another macro to do that
Else
Application.Goto Sheets(WantedSheet).Range("a4")
End If
Application.DisplayAlerts = True
End Sub

Or, to do it the way you suggest, use a worksheet_CHANGE event restricted
to
cell a1






--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Boss" wrote in message
...
Hi,

I have 25 sheet named Sheet 1 to Sheet 25.
What i am looking for is..

I need to select the specific sheet whos name is written in sheet1 A1
cell.

For example if Sheet 1 cell A1 = sheet2 and i run the macro it should
take
me to sheet 2

and if Sheet 1 cell A1 = sheet14 and i run the macro it should take me
to
sheet 14.

So on and so forth.

I had a similar macro but i lost it. please help me..
Thanks a lot
Boss