VBA to Goto column reference number pre-typed in cell A1

[omairhe]

Hey everyone, hope all is good,

I need a VBA code that would run on a worksheet when it gets selected.

The user will select a sheet and right after it is selected the code must check the value in cell A1 of sheet named= "Hosting". The value is going to be numerical, for example 1, 2, 3 , 4 and so on. If value is 3 then take the user to column no. 3 which is column C of the selected sheet. If value is 9 then take me to column 9 which is column I in the newly selected sheet.

My workbook consists of multiple worksheets so perhaps a Workbook code will be a better option, instead of inserting the code in each of the sheets?

Thank you, help on the matter will be highly appreciated.

 

[Fluff]

How about

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
   Sh.Cells(1, Sheets("Hosting").Range("A1").Value).Activate
End Sub

 

[omairhe]

How about

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
   Sh.Cells(1, Sheets("Hosting").Range("A1").Value).Activate
End Sub

Dear Fluff,

Thank you for the help. Also the row number should remain undisturbed. meaning if cell value A1 = 5
and new sheet was at B16 , the code should now take me to E16, not E1

Sorry that I couldn't make that clear in my original post.

 

[Fluff]

How about

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
   Sh.Cells(ActiveCell.Row, Sheets("today").Range("A1").Value).Activate
End Sub

 

[omairhe]

How about

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
   Sh.Cells(ActiveCell.Row, Sheets("today").Range("A1").Value).Activate
End Sub

Great. Thanks again.

 

[Fluff]

You're welcome & thanks for the feedback