How to synchronize all worksheets of the workbook with same range selected in Excel?

When we use an excel workbook, sometimes, we need to select the same ranges of multiple worksheets in a workbook and synchronize them to look at the information easily and conveniently. The following methods will make you synchronize worksheets to have the same ranges in a workbook quickly and easily.

Select same range in all worksheets with Select All Sheets;

Using VBA code to synchronize worksheets;

Quickly synchronize all worksheets to the same range with one click.

Select same range in all worksheets with Select All Sheets

You can make all worksheets to have the same range selected in a workbook as follows:

1. Select a range in the active worksheet you want to select in all worksheets. For example A1:H14.

2. Then Right-Clicks on the worksheet tab, and choose Select All Sheets from the menu. See screenshot:

3. All the worksheets in the workbook will have the same range selected.

Note: to select same range in all worksheets with this way, it can only select the same range in all worksheets, but it cannot display all the selected ranges in the same position of the window.

Using VBA code to synchronize worksheets

Using the following VBA code, you can quickly make all worksheets to have same range selected and display the selected range in the same position of the window.

1. Select a range in one worksheet, and then click Developer >Visual Basic, a new Microsoft Visual Basic for applications window will be displayed, click Insert > Module, and then copy and paste the following codes in the module:

Sub SynchSheets()
If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
Dim UserSheet As Worksheet, sht As Worksheet
Dim TopRow As Long, LeftCol As Integer
Dim UserSel As String
Application.ScreenUpdating = False
Set UserSheet = ActiveSheet
TopRow = ActiveWindow.ScrollRow
LeftCol = ActiveWindow.ScrollColumn
UserSel = ActiveWindow.RangeSelection.Address
For Each sht In ActiveWorkbook.Worksheets
If sht.Visible Then
sht.Activate
Range(UserSel).Select
ActiveWindow.ScrollRow = TopRow
ActiveWindow.ScrollColumn = LeftCol
End If
Next sht
UserSheet.Activate
Application.ScreenUpdating = True
End Sub

2. Then click  button to execute the code, all of the worksheets will have the same range selected and display the selected ranges in the same position of window.

Quickly synchronize all worksheets to the same range with one click

If you have installed Kutools for Excel, you can use the Synchronize Worksheets feature to easily synchronize all worksheets to have the same range as follows:

Kutools for Excel: with more than 80 handy Excel add-ins, free to try with no limitation in 30 days. Get it Now

1. Active a worksheet and select the range. Click Kutools > Worksheet Tools > Synchronize Worksheets. Click OK.

2. You will all the worksheets with the same range selected and display the same range in the same position in the window.

For more detailed information about Synchronize Worksheets, please visit here.

Kutools for Excel

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.