Microsoft Office Tutorials and References
In Depth Information
Working with Workbooks and Sheets
A workbook with this example is available on the companion CD-ROM. The file is
named hide rows and columns.xlsm .
Synchronizing worksheets
If you use multisheet workbooks, you probably know that Excel can’t 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:
h Selects the same range as the active sheet.
h Makes the upper-left cell the same as the active sheet.
Following is the listing for the procedure:
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
 
Search JabSto ::




Custom Search