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.
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks