24、Excel VBA实用技巧与代码示例

Excel VBA实用技巧与代码示例

1. 同步工作表

在使用多工作表工作簿时，Excel 本身无法自动同步工作表。下面的 VBA 宏以活动工作表为基础，让工作簿中的其他工作表与活动工作表具有相同的选定范围和左上角单元格。

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