各位同学，如何将多个工作表的内容合并到一个汇总表内

chenxu0127

大家好，想在每当表1、表2的数据变动时，汇总表会自动合并记录表1表2的内容，自己百度了半天也没弄出来，真心谢谢大家了。

qkhhynixa

借论坛高手的方法，忘记那位高手的名字了

iwangyi

Sub C()
Set CN = CreateObject("ADODB.CONNECTION")
Set D = CreateObject("SCRIPTING.DICTIONARY")
CN.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;EXTENDED PROPERTIES=EXCEL 8.0;DATA SOURCE =" & ThisWorkbook.FullName
For Each SH In Sheets
If SH.Name <> "汇总表" Then
Sql = "SELECT * FROM [" & SH.Name & "$]"
D(Sql) = ""
End If
Next
Sql = Join(D.KEYS, " UNION ALL ")
Range("A2").CopyFromRecordset CN.Execute(Sql)
End Sub

iwangyi

Sub CC()
For Each SH In Sheets
N = [B65536].End(xlUp).Row + 1
If SH.Name <> "汇总表" Then
SH.[A1].CurrentRegion.Offset(1).Copy Range("A" & N)
End If
Next
End Sub

iwangyi

附件请参考***

chenxu0127

iwangyi 发表于 2019-12-22 08:10
附件请参考***

谢谢