Synchronizing worksheets

If you use multisheet workbooks, 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 that follows 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. Following is the listing for the subroutine:

Sub SynchSheets()

' Duplicates the active sheet's active cell and upper left 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

CD-ROM

A workbook with this example is available on the companion CD-ROM in a file named synchronize sheets.xlsm .

0 0

Post a comment