| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Linking Tab/worksheet names to a worksheet cell | Excel Discussion (Misc queries) | |||
| link a cell in the 'Master' worksheet list to a 'Detail' worksheet | Excel Programming | |||
| error with macro to name new worksheet with cell from old worksheet | Excel Programming | |||
| How can I link cell colours from worksheet to worksheet/workbook? | Excel Worksheet Functions | |||
| selecting cell range in other worksheet without switching to worksheet | Excel Programming | |||
Linear Mode
