select same column across multiple sheets

[omairhe]

Hey everyone.

What VBA code can take me to the same column as my current worksheet column I am working on. For instance, I am at column D of my Active worksheet. So if I type in "Enter". The Enter key pressed on Column D should make the Column D the active column in all other sheets. Point to be noted here is I do not want the rows to be disturbed. Meaning if in other sheets the rows were at C2, E6 and F10 prior pressing the Enter key, I want these to be D2, D6 and D10 respectively.


Would it be possible?

Thanks and much appreciation.

 

[Tetra201]

Try placing the following sub in ThisWorkbook code section:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In Sheets
        ws.Select
        Cells(ActiveCell.Row, Source.Column).Select
    Next
    Sh.Select
    Application.ScreenUpdating = True
End Sub

 

[omairhe]

Try placing the following sub in ThisWorkbook code section:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In Sheets
        ws.Select
        Cells(ActiveCell.Row, Source.Column).Select
    Next
    Sh.Select
    Application.ScreenUpdating = True
End Sub

Sorry to bug u but please also note that along with what your code does, which is phenomenon, it must also have the scrolling position set for the column as same as the active worksheet.
For instance I change the value in cell AH3 and press Enter. The code takes over from here and selects AH for the rest of the sheets, but I can't view the column unless I press an arrow key first, because it doesn't automatically scroll to the selected cell or keep the same scroll as the original sheet.

 

[Tetra201]

Here is a modified sub for you to try:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
    Dim ws As Worksheet, c As Long
    c = ActiveWindow.VisibleRange.Column
    Application.ScreenUpdating = False
    For Each ws In Sheets
        ws.Select
        Cells(ActiveCell.Row, Source.Column).Select
        ActiveWindow.SmallScroll ToLeft:=Columns.Count
        ActiveWindow.SmallScroll ToRight:=c - 1
    Next
    Sh.Select
    Application.ScreenUpdating = True
End Sub

 

[omairhe]

Here is a modified sub for you to try:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
    Dim ws As Worksheet, c As Long
    c = ActiveWindow.VisibleRange.Column
    Application.ScreenUpdating = False
    For Each ws In Sheets
        ws.Select
        Cells(ActiveCell.Row, Source.Column).Select
        ActiveWindow.SmallScroll ToLeft:=Columns.Count
        ActiveWindow.SmallScroll ToRight:=c - 1
    Next
    Sh.Select
    Application.ScreenUpdating = True
End Sub

Perfect. I am satisfied with this one.
Thank you.

 

[Tetra201]

You are welcome.