|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
上传代码:
- Sub 汇总数据()
- Dim arr, i As Long, wb As Workbook, mRow As Long, mCol As Integer, bookName As String
- Dim ThisBook As Workbook: Set ThisBook = ThisWorkbook
- Dim mPath As String: mPath = ThisBook.Path & "\"
- Application.ScreenUpdating = False
- ThisBook.Worksheets("Sheet1").Range("F:XFD").ClearContents
- Dim mFile As String: mFile = Dir(mPath & "*.xlsx")
- Do
- If mFile <> ThisBook.Name Then
- Set wb = GetObject(mPath & mFile)
- With wb
- bookName = Split(.Name, ".")(0)
- With .Worksheets("Sheet1")
- mRow = .Range("F1048576").End(3).Row
- arr = .Range("F2:F" & mRow).Value
- End With
- .Close False
- End With
- With ThisBook.Worksheets("Sheet1")
- .Activate
- mCol = .Cells.Find("*", , , , 2, 2).Column + 1
- .Cells(1, mCol).Value = bookName
- .Cells(2, mCol).Resize(UBound(arr), 1).Value = arr
- End With
- End If
- mFile = Dir
- Loop While mFile <> ""
- Application.ScreenUpdating = True
- End Sub
复制代码
|
|