|
发表于 2016-11-23 14:07
|
显示全部楼层
本楼为最佳答案
- Sub tt()
- Dim filePath As String, shName As String
- Dim wbName As String, wb As Workbook, sh As Worksheet
- Dim lstRow%, staRow%, mbRow&, i%, n%
- Application.ScreenUpdating = False
- Sheet1.Range("a2:m65536").ClearContents
- filePath = ThisWorkbook.Path & "\原始数据"
- wbName = Dir(filePath & "*.xls*")
- Do While wbName <> ""
- Set wb = Workbooks.Open(filePath & wbName, True, True)
- For Each sh In wb.Worksheets
- staRow = sh.Range("A:A").Find("关键词").Row + 2
- lstRow = sh.Range("a65536").End(3).Row
- mbRow = Sheet1.Range("a65536").End(3).Row
- arr = sh.Range(Cells(staRow, 1), Cells(lstRow, 13))
- n = 0
- For i = 1 To UBound(arr)
- If arr(i, 1) <> "" Then
- n = n + 1
- Sheet1.Cells(mbRow + n, 1) = Right(Split(wbName, ".")(0), 10)
- Sheet1.Cells(mbRow + n, 2).Resize(, 13) = Application.Index(arr, i)
- End If
- Next
- wb.Close: Set wb = Nothing: Erase arr
- Next
- wbName = Dir
- Loop
- Application.ScreenUpdating = True
- End Sub
复制代码
试做了下,单元格格式没有设置 |
|