Excel Developer Tip

Excel page

Tip archives

Synchronizing Sheets in a Workbook

If you use multisheet workbook, you probably know that Excel cannot "synchronize" the sheets in a workbook. In other words, there is no automatic way to force all sheets to have the same selected range and upper left cell.

The VBA macro listed below uses the active worksheet as a base, and then performs the following on all other worksheets in the workbook:

• Selects the same range as the active sheet
• Makes the upper left cell the same as the active sheet

Note: Hidden worksheet are ignored.

The SynchSheets Subroutine

Following is the listing for the subroutine:

Sub SynchSheets()
'   Duplicates the active sheet's active cell upperleft cell
'   Across all worksheets
    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

'   Remember the current sheet
    Set UserSheet = ActiveSheet
    
'   Store info from the active sheet
    TopRow = ActiveWindow.ScrollRow
    LeftCol = ActiveWindow.ScrollColumn
    UserSel = ActiveWindow.RangeSelection.Address
    
'   Loop through the worksheets
    For Each sht In ActiveWorkbook.Worksheets
        If sht.Visible Then 'skip hidden sheets
            sht.Activate
            Range(UserSel).Select
            ActiveWindow.ScrollRow = TopRow
            ActiveWindow.ScrollColumn = LeftCol
        End If
    Next sht
    
'   Restore the original position
    UserSheet.Activate
    Application.ScreenUpdating = True
End Sub

Using the Subroutine

To use this subroutine, copy it to a VBA module (your Personal Macro Workbook is a good choice). Then, activate a worksheet and execute the SynchSheets subroutine. All of the worksheets will then have the same range selection and and upper left cell as the active sheet.