|
- Sub 汇总()
- Dim Wk As Workbook, myPath$, MyName$, i&
- Dim Sht As Worksheet, col&, j&
- Application.ScreenUpdating = False
- Set Sht = ActiveSheet
- Range("m6:r47", "t6:y47").ClearContents
- myPath = ThisWorkbook.Path & "\退货数据DATA"
- MyName = Dir(myPath & "*.xls"): i = 6: col = 13
- Do While MyName <> ""
- Set Wk = Workbooks.Open(myPath & "" & MyName)
- arr = Wk.Sheets(1).Range("B3:G" & Wk.Sheets(1).Range("B65536").End(3).Row)
- j = 0
- Do
- j = j + 1
- If i > 47 Then
- col = col + 7: i = 6
- If col > 20 Then MsgBox "数据溢出无法显示汇总全部内容。": Exit Sub
- End If
- If j <= UBound(arr) Then
- Sht.Cells(i, col).Resize(1, 6) = Application.Index(arr, j, 0): i = i + 1
- Else
- Exit Do
- End If
- Loop While col < 21
- Wk.Close False
- MyName = Dir
- Loop
- Columns("q:q").NumberFormatLocal = "#,##0.00"
- Columns("x:x").NumberFormatLocal = "#,##0.00"
- Columns("r:r").NumberFormatLocal = "¥#,##0.00"
- Columns("y:y").NumberFormatLocal = "¥#,##0.00"
- Application.ScreenUpdating = True
- MsgBox "汇总完毕"
- End Sub
复制代码 |
|