go to based on cell value

[projectile]

Hi

I have an excel sheet with a list name ranges in entire workbook, and wanted to create a macro, so that when the user click on the one of these cells in sheet, it would jump to the named range.

e.g. the value of cell A1 is septweek1. septweek1 is actually a named range that points to =dept1!$B$6

The column that contains the list of named ranges is A:A

this is the code I have tried, but doesn't work

Private Sub Worksheet_Activate()

Dim a As String
a = ActiveCell.Value
Application.Goto Reference:=a
End Sub

 

[VoG]

Try

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then Application.Goto Range(Target.Value)
End Sub

 

[projectile]

Hi

I get this error: method 'Range' of object 'worksheet' failed.

Any ideas?

Thanks

 

[projectile]

managed to figure out, thanks

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then Application.Goto reference:=Target.Value
End Sub

 

[tusharm]

What is in the cell you are clicking in?

Is that a legitimate named reference?

Hi

I get this error: method 'Range' of object 'worksheet' failed.

Any ideas?

Thanks

 

[projectile]

Hi , this was a legitimate named reference. worked with other code.

Problem with the working code, is the go to cell reference is not appearing in the top left of the sheet, so we have to scroll to see the info we have jumped to.

Any ideas on how to correct this?

Thanks

 

[VoG]

Try

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then Application.Goto reference:=Target.Value, scroll:=True
End Sub

 

[projectile]

thanks, worked a treat!