|
首先把所有要汇总的表放到一个文件夹里。- Sub 导入文件()
- Application.ScreenUpdating = False
- Dim filename, wb As Workbook, Sht As Worksheet
- filename = Dir(ThisWorkbook.Path & "\*.xls")
- Set sh = ActiveSheet
- Do While filename <> ""
- If filename <> ThisWorkbook.Name Then
- fn = ThisWorkbook.Path & "" & filename
- Set wb = Workbooks.Open(fn)
- Set Sht = wb.Worksheets(1)
- r = Sht.[a65536].End(3).Row
- r1 = sh.[a65536].End(3).Row
- Sht.Range("a2:L" & r).Copy sh.Cells(r1 + 1, 1) '把所有文件内容复制到当前工作表
- wb.Close False
- End If
- filename = Dir
- Loop
- Set Sht = Nothing
- With sh '把当前工作表中欠款为0的行去掉
- r = .[a65536].End(3).Row
- Set delrng = .Rows(r + 1)
- For i = 3 To r
- If Val(.Cells(i, "L")) = 0 Then Set delrng = Union(delrng, .Rows(i))
- Next
- delrng.Delete
- End With
- Application.ScreenUpdating = True
- End Sub
复制代码 |
|