总表与分表相互更新（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