|
发表于 2011-11-1 17:50
|
显示全部楼层
本楼为最佳答案
新增红色那句!
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
Sht.Cells(i, col).Offset(-1, 5) = Format(Sht.Cells(i, col).Offset(-1, 5), "0.00")
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 |
|