VBA Macro To Select Same Cell on all Worksheets

Question

I'm somewhat newer to VBA, and this particular action seems like it may be out of my current scope of knowledge.

Is there a way to code VBA to have it actively select the same cell on all worksheets as the current cell selected? I have a model I've put together to allow my team to enter data simultaneously regarding product SKUs in Column A on Sheet1, but due to the large amount of information that we enter per item, I used multiple sheets

For example, if I have cell H4 selected on Sheet1, is it possible to have all other sheets active cell H4 upon switching to the other worksheets?

This is what I've come up with so far on a test workbook, but it does not seem to work:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Select Case LCase(Sh.Name)
Case Is = "sheet1", "sheet2", "sheet3"
    If CurRow > 0 Then
    With Application
        .EnableEvents = False
        .Goto Sh.Cells(CurRow, CurCol), Scroll:=True
        Sh.Range(ActCellAddr).Select
        .EnableEvents = True
    End With
    End If
    End Select

End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Select Case LCase(Sh.Name)
    Case Is = "sheet1", "sheet2", "sheet3"
        CurRow = ActiveWindow.ScrollRow
        CurCol = ActiveWindow.ScrollColumn
    ActCellAddr = ActiveCell.Address
    End Select

End Sub

I've located this code below:

Excel VBA code to allow the user to choose the same cell on every sheet

But this requires the user actually enter the cell they'd like to have selected. I am looking for it to be automatic.

Any tips or suggestions? Any help is greatly appreciated.

Answer 1

This approach will test for hidden sheets. It selects all non-hidden sheets, selects the target cell then returns to the original sheet. It works pretty fast even if you have many many tabs.

targetcell = ActiveCell.Address
OriginSheet = ActiveSheet.Name
Dim ws As Worksheet
For Each ws In Sheets
    If ws.Visible = True Then ws.Select (False)
Next ws
range(targetcell).Select
Sheets(OriginSheet).Select

Answer 2

You can post the following to every sheet in your workbook.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Set CurrWS = ActiveSheet
    For Each WS In ThisWorkbook.Worksheets
        WS.Activate
        WS.Range(Target.Address).Select
    Next
    CurrWS.Activate

End Sub

Every time you select a cell, it will cycle through all the worksheets and select the same cell there. The downside to this is obvious: if you have too many sheets, it's going to be tedious. The other problem is that it's going to cycle through everything. So it might mess up some other sheets if you're going to use this for data entry.

Otherwise, if it's just selecting the cell, then this is harmless though the flicker can be noticeable at times, based on how many sheets you have.

Not as elegant as one would want, but it works. Good luck and let us know if this helps.

Answer 3

Worth noting there is a workbook-level event handler which handles the same event, so you only need to add the code once to the ThisWorkbook code module:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
                                          ByVal Target As Range)

Sh represents the ActiveSheet.

Probably also worth disabling events while you're selecting the ranges on the other sheets, or that will re-trigger your event handler (don't forget to turn event handling back on before exiting your code!)