如何筛选多表中相同类别数据汇总到另一表

foxlyy

如何把多个表中相同类别的数据，汇总到另一表中

lsc900707

是求助帖吧，怎么选了分享？

魂断蓝桥

Sub 查询()
Dim cnn As Object
Dim Sql As String, sh As Worksheet
Set cnn = CreateObject("ADODB.CONNECTION")
For Each sh In Sheets
    If sh.Name <> "汇总" And sh.Name <> "种类" Then
        Sql = Sql & "select """ & sh.Name & """ as 店名,货名,种类 from [" & sh.Name & "$a1:b]  UNION "
    End If
Next
Sql = Left(Sql, Len(Sql) - 6)
Sql = "SELECT 店名,货名 FROM (" & Sql & ") WHERE 种类='" & Sheet4.[B1] & "'"
cnn.Open "Provider=Microsoft.ACE.OleDb.12.0;Extended Properties='Excel 12.0;HDR=YES'; Data Source=" & ThisWorkbook.FullName
Range("A4:B999").ClearContents
[A4].CopyFromRecordset cnn.Execute(Sql)
cnn.Close: Set cnn = Nothing
End Sub

foxlyy

选错了分享？第一次发贴，发错了，抱歉

foxlyy

魂断蓝桥 发表于 2018-3-28 08:29
Sub 查询()
Dim cnn As Object
Dim Sql As String, sh As Worksheet

请问，Sub 查询()这个是VBA，可以实现，谢谢啦，“数组公式==........选择存放位置－完成”这个是如何实现？