How to go to or view same cell on all worksheets/tabs

[a_dzhidaryan]

Hello,

Hope everyone is well.

Is it possible to have the view of all tabs/worksheets be the same?

I tried doing the method of selecting all tabs (hold down control, click on tabs) and then going to a specified cell. The problem I have is that it might go to that cell for those tabs, but it's still scrolled to a different section.

So to summarize, I want to be able to go to and have the same view on all tabs.

Appreciate your help.

 

[My Aswer Is This]

Try using this Vba script:

Sub Test()
Application.ScreenUpdating = False
Dim i As Long
Dim ans As String
ans = InputBox("Enter Range to Goto  Like G40")
    For i = 1 To Sheets.Count
        Application.Goto Reference:=Worksheets(i).Range(ans), scroll:=True
    Next
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

Or try this script:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on your Master sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Now when you double click on any cell your script will run.
The script will ask where you want to go on every sheet.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Application.ScreenUpdating = False
Cancel = True
Dim i As Long
Dim ans As String
ans = InputBox("Enter Range to Goto  Like G40")
    For i = Sheets.Count To 1 Step -1
        Application.Goto Reference:=Worksheets(i).Range(ans), scroll:=True
    Next
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

If you want to use the double click script in post #3
And you want the GOTO cell centered on the screen best as possible try this script.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Application.ScreenUpdating = False
Cancel = True
Dim b As Long
Dim ans As String
ans = InputBox("Enter Range to Goto  Like G40")
    For b = Sheets.Count To 1 Step -1
        Application.Goto Reference:=Worksheets(b).Range(ans), scroll:=True
                
        With ActiveWindow
            i = .VisibleRange.Rows.Count / 2
            j = .VisibleRange.Columns.Count / 2
            .SmallScroll Up:=i, ToLeft:=j
        End With
        
    Next
Application.ScreenUpdating = True
End Sub

 

[a_dzhidaryan]

Thank you so much!

This is excellent.

May I get some final assistance with integrating something in the code for it to return to the very first tab/sheet once it's gone through all of the tab/sheets.

I'm guessing the line of code would go right before the Application.ScreenUpdating = True

I tried recording a macro to figure out how to get back to the first sheet/tab, but the code was specific to the name of the tab and I was hoping for something that's more general that I could use for various files that has different names for the tabs.

Let me know if I can better clarify.

 

[My Aswer Is This]

Try this:

I'm assuming you decided to use the Module script and not the double click sheet event scripts I provided.

This script will do what you want and center the selection on the screen best it can.

Sub Goto_And_Center()
Application.ScreenUpdating = False
Dim b As Long
Dim ans As String
ans = InputBox("Enter Range to Goto  Like G40")
    For b = Sheets.Count To 1 Step -1
        Application.Goto Reference:=Worksheets(b).Range(ans), scroll:=True
    
   With ActiveWindow
            i = .VisibleRange.Rows.Count / 2
            j = .VisibleRange.Columns.Count / 2
            .SmallScroll Up:=i, ToLeft:=j
        End With
 
    Next
Application.ScreenUpdating = True
End Sub