|
楼主 |
发表于 2019-6-21 17:22
|
显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
- Sub test()'来自褚老师
- Dim r%, i%
- Dim arr, brr
- Dim mypath$, myname$
- Dim wb As Workbook
- Dim ws As Worksheet
- Dim d As Object
- Dim rng1 As Range
- Dim rng2 As Range
- Set d = CreateObject("scripting.dictionary")
- Dim reg As New RegExp
- tt = Timer
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- mypath1 = ThisWorkbook.Path & "\原始数据"
- mypath2 = ThisWorkbook.Path & "\需生成的效果数据"
- myname = Dir(mypath1 & "*.xls")
- With reg
- .Pattern = "\d{4}"
- End With
- With Worksheets("sheet1")
- Set rng1 = .Range("a1:K7")
- Set rng2 = .Range("a8:K8")
- End With
- Do While myname <> ""
- If reg.test(myname) Then
- Set mh = reg.Execute(myname)
- nf = mh(0)
- Set wb = GetObject(mypath1 & myname)
- With wb
- With .Worksheets("sheet1")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- If .Cells(r, 1).MergeCells Then
- r = r - 1
- End If
- If InStr(.Range("a1"), "附件") <> 0 And r > 7 Then
- arr = .Range("a1:L" & r)
- For i = 8 To UBound(arr)
- If Not d.exists(arr(i, 12)) Then
- Set d(arr(i, 12)) = CreateObject("scripting.dictionary")
- End If
- If Not d(arr(i, 12)).exists(nf) Then
- m = 1
- ReDim brr(1 To UBound(arr, 2), 1 To m)
- Else
- brr = d(arr(i, 12))(nf)
- m = UBound(brr, 2) + 1
- ReDim Preserve brr(1 To UBound(arr, 2), 1 To m)
- End If
- brr(1, m) = m
- For j = 2 To UBound(arr, 2)
- brr(j, m) = arr(i, j)
- Next
- d(arr(i, 12))(nf) = brr
- Next
- End If
- End With
- .Close False
- End With
- End If
- myname = Dir()
- Loop
- For Each aa In d.keys
- Application.SheetsInNewWorkbook = d(aa).Count
- Set wb = Workbooks.Add
- With wb
- m = 0
- For Each bb In d(aa).keys
- m = m + 1
- brr = d(aa)(bb)
- ReDim crr(1 To UBound(brr, 2), 1 To UBound(brr))
- For i = 1 To UBound(brr)
- For j = 1 To UBound(brr, 2)
- crr(j, i) = brr(i, j)
- Next
- Next
- With .Worksheets(m)
- .Name = bb
- rng1.Copy .Range("a1")
- .Range("a8").Resize(UBound(crr), UBound(crr, 2)) = crr
- r = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
- rng2.Copy .Cells(r, 1)
- .Range("a4:K" & r - 1).Borders.LineStyle = xlContinuous
- .Rows("1:" & r - 1).RowHeight = 25.5
- .Rows(r).RowHeight = 65.25
- .Buttons.Delete
- End With
- Next
- .SaveAs Filename:=mypath2 & aa & ".xls", FileFormat:=xlExcel8
- .Close False
- End With
- Next
- Application.ScreenUpdating = True
- MsgBox "统计完毕!共用时" & Timer - tt & "秒"
- End Sub
复制代码 |
|