excel - 如何从几个excel文件中获取信息到某个列的主文件中？

Question

再会!我有一个代码可以复制一些文件中的所有信息并将其插入到主文件中。我想修改它。不是抄袭，而是总结。

Dim MyFile As String, Str As String, MyDir As String, Wb As Workbook
Dim Rws As Long, Rng As Range, r as Range
Set Wb = ThisWorkbook

MyDir = "C:\Project\"
MyFile = Dir(MyDir & "*.xlsx") 
ChDir MyDir
Application.ScreenUpdating = 0
Application.DisplayAlerts = 0
 
Do While MyFile <> ""
    Workbooks.Open (MyFile)
    For Each r in Rows
    With Worksheets("Sheet1")
        r.Rows.Hidden = False
        Rws = .Cells(Rows.Count, 12).End(xlUp).Row
        Set Rng = Range(.Cells(5, 12), .Cells(Rws, 12))
        Rng.Copy Wb.Worksheets("Sheet1").Cells(Rows.Count, 12).End(xlUp).Offset(0, 1) 'So here I copy infromation adn insert it.
        ActiveWorkbook.Close True
    End With
    Next
    Application.DisplayAlerts = 1
    MyFile = Dir()
Loop

我无法理解它。我如何总结相同的信息而不是复制它。
我并不是说将所有行汇总到主文件中，我想将不同文件中的相同行汇总到一个主文件中。
另一件事，很少有文件包含隐藏行，也许我做错了什么，但这些行仍然被隐藏

r.Rows.Hidden = False

似乎没有做这件事。
任何帮助将非常感激
结构:所有文件在结构上都是相同的，主文件是同一个文件，但其中没有任何信息，只是标题。 - 前 4 行 第 12 列(“L”) - 最后一个有我需要的信息，或者我应该说的信息。每行都有一个与其他行不同的 ID(第 1 列 - “A”)。所有文件都非常相似，这些行中的项目是相同的，唯一的区别是最后一列中的数量，这是我需要计算的。主文件:行 - 项目 - 来自所有其他文件的数量。

Answer 1

你可以试试这个:

Dim MyFile As String, Str As String, MyDir As String, Wb As Workbook
Dim Rws As Long, Rng As Range, r As Range
Set Wb = ThisWorkbook

MyDir = "C:\Project\"
MyFile = Dir(MyDir & "*.xlsx")
ChDir MyDir
Application.ScreenUpdating = False
Application.DisplayAlerts = False
 
Do While MyFile <> ""
    
    Dim sWb As Workbook
    Set sWb = Workbooks.Open(MyFile)
    
    With sWb.Worksheets("Sheet1")
        .Rows.Hidden = False
        Rws = .Cells(Rows.Count, 12).End(xlUp).Row
        Set rng = Range(.Cells(5, 1), .Cells(Rws, 12))
    End With
    
    With Wb.Worksheets("Sheet1")
        Dim MatchingColumn As Range
        Set MatchingColumn = .Range(.Cells(5, 1), .Cells(.Rows.Count, 1).End(xlUp))
    
        For Each r In rng.Rows
            If r.Cells(1, 1).Value2 <> vbNullString Then 'Ignoring empty rows
                
                'We find the row where the Ids matche
                Dim MatchingRowNb As Long
                MatchingRowNb = Application.Match(r.Cells(1, 1).Value2, MatchingColumn, False)
                    
                'We add the current value in the cell with the new value comming from the other file
                .Cells(4 + MatchingRowNb, 12).Value2 = .Cells(4 + MatchingRowNb, 12).Value2 + r.Cells(1, 12).Value2
            End If
        Next
    End With
    
    sWb.Close SaveChanges:=True

    Application.DisplayAlerts = True
    MyFile = Dir()
Loop