Looking for worksheet according to name in cell
 

Hi, Just a quickie (I hope)
I have a cell that has been given a range name (Reg_no) How can a Worksheet
be selected according to the info within the Ranged cell?
The workbook has many tabs, and what I want is a piece of code that will
take me to the tab with the same name that is within the Ranged cell.
Help greatly appreciated
John

Looking for worksheet according to name in cell
 

Right click sheet tabview codecopy/paste thistype the name of the sheet
into a cell and double click on it

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(ActiveCell.Value) Is Nothing Then
GetWorkbook ' calls another macro to do that
Else
Application.Goto Sheets(ActiveCell.Value).Range("a4")
End If
Application.DisplayAlerts = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JohnUK" wrote in message
...
Hi, Just a quickie (I hope)
I have a cell that has been given a range name (Reg_no) How can a
Worksheet
be selected according to the info within the Ranged cell?
The workbook has many tabs, and what I want is a piece of code that will
take me to the tab with the same name that is within the Ranged cell.
Help greatly appreciated
John

Looking for worksheet according to name in cell
 

Hi Don, Many thanks for your help.
I tried the code below and it works a treat on its own, but is there some
way I can incorporate it into an existing macro, because the rest of the
macro copies data from the main tab and needs to know what tab to paste it
to.
Thanks


"Don Guillett" wrote:

Right click sheet tabview codecopy/paste thistype the name of the sheet
into a cell and double click on it

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(ActiveCell.Value) Is Nothing Then
GetWorkbook ' calls another macro to do that
Else
Application.Goto Sheets(ActiveCell.Value).Range("a4")
End If
Application.DisplayAlerts = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JohnUK" wrote in message
...
Hi, Just a quickie (I hope)
I have a cell that has been given a range name (Reg_no) How can a
Worksheet
be selected according to the info within the Ranged cell?
The workbook has many tabs, and what I want is a piece of code that will
take me to the tab with the same name that is within the Ranged cell.
Help greatly appreciated
John

Looking for worksheet according to name in cell
 

Ah - I have it, and thanks to you.
I used the:
Application.Goto Sheets(ActiveCell.Value).Range("B2")
Many thanks Don

Take care
John

"JohnUK" wrote:

Hi Don, Many thanks for your help.
I tried the code below and it works a treat on its own, but is there some
way I can incorporate it into an existing macro, because the rest of the
macro copies data from the main tab and needs to know what tab to paste it
to.
Thanks


"Don Guillett" wrote:

Right click sheet tabview codecopy/paste thistype the name of the sheet
into a cell and double click on it

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(ActiveCell.Value) Is Nothing Then
GetWorkbook ' calls another macro to do that
Else
Application.Goto Sheets(ActiveCell.Value).Range("a4")
End If
Application.DisplayAlerts = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JohnUK" wrote in message
...
Hi, Just a quickie (I hope)
I have a cell that has been given a range name (Reg_no) How can a
Worksheet
be selected according to the info within the Ranged cell?
The workbook has many tabs, and what I want is a piece of code that will
take me to the tab with the same name that is within the Ranged cell.
Help greatly appreciated
John

Looking for worksheet according to name in cell
 

Glad you got it sorted


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JohnUK" wrote in message
...
Ah - I have it, and thanks to you.
I used the:
Application.Goto Sheets(ActiveCell.Value).Range("B2")
Many thanks Don

Take care
John

"JohnUK" wrote:

Hi Don, Many thanks for your help.
I tried the code below and it works a treat on its own, but is there some
way I can incorporate it into an existing macro, because the rest of the
macro copies data from the main tab and needs to know what tab to paste
it
to.
Thanks


"Don Guillett" wrote:

Right click sheet tabview codecopy/paste thistype the name of the
sheet
into a cell and double click on it

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(ActiveCell.Value) Is Nothing Then
GetWorkbook ' calls another macro to do that
Else
Application.Goto Sheets(ActiveCell.Value).Range("a4")
End If
Application.DisplayAlerts = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JohnUK" wrote in message
...
Hi, Just a quickie (I hope)
I have a cell that has been given a range name (Reg_no) How can a
Worksheet
be selected according to the info within the Ranged cell?
The workbook has many tabs, and what I want is a piece of code that
will
take me to the tab with the same name that is within the Ranged cell.
Help greatly appreciated
John