Looking for worksheet according to name in cell

J

JohnUK

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
 
D

Don Guillett

Right click sheet tab>view code>copy/paste this>type 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
 
J

JohnUK

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
 
J

JohnUK

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

Take care
John
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top