總表與分表互相更新（Excel代碼集團）

問題：總表資料手動更新後，分表一鍵更新，同時分表手動更新後，總表一鍵更新。

解決思路：分别寫兩段代碼，總表拆分用于前者，分表彙總用于後者。

資料源8列若幹行，按第一列進行拆分。

更新分表的代碼：

Sub Spt()
Dim MyShn As Long, MyRow As Long
Range("a1").AutoFilter
MyRow = Cells(Rows.Count, 1).End(xlUp).Row
    For MyShn = 2 To Sheets.Count
        With Sheets(MyShn)
            .Cells.ClearContents
            Range("a1").Resize(MyRow, 8).AutoFilter Field:=1, Criteria1:=.Name
            Range("a1").Resize(MyRow, 8).Copy Destination:=.Range("a1")
        End With
    Next
    Range("a1").AutoFilter
End Sub      

更新總表的代碼：

Sub Comb()
Dim MyShn As Long, MyRow As Long, i As Long
Range("a2").Resize(Cells(Rows.Count, 1).End(xlUp).Row, 8).ClearContents
i = 2
For MyShn = 2 To Sheets.Count
    With Sheets(MyShn)
        MyRow = .Cells(Rows.Count, 1).End(xlUp).Row
        .Range("a2").Resize(MyRow, 8).Copy Destination:=Cells(i, 1)
        i = MyRow + i - 1
    End With
Next
End Sub