|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
- Sub qq()
- Dim rng, d, r%, i%, sht As Worksheet, s As Date
- Dim x%, n%, aa, ws As Worksheet
- Set d = CreateObject("Scripting.Dictionary")
- Application.DisplayAlerts = False
- With ActiveSheet
- For Each sht In Sheets
- If sht.Name <> .Name Then sht.Delete
- Next
- Set rng = .[a1].Resize(4, 10)
- r = .Cells(Rows.Count, 1).End(xlUp).Row
- n = 1
- For i = 5 To r
- If .Cells(i, 8).Value >= 600 Then
- x = x + 1
- Set d("合格" & x) = Union(rng, .Cells(i - n + 1, 1).Resize(n, 10))
- n = 1
- Else
- n = n + 1
- End If
- Next
- End With
- For Each aa In d.keys
- Set ws = Worksheets.Add(after:=Worksheets(Worksheets.Count))
- With ws
- .Name = aa
- d(aa).Copy .Range("a1")
- .[a3] = Left(.[a3].Value, Application.Find("考核日期:", .[a3].Value) + 4) & Format(.[a5].Value, "yyyy年m月d日")
- Set rng = .Cells(Rows.Count, 1).End(xlUp)
- .Columns("a").AutoFit
- s = Format(DateSerial(Year(rng), Month(rng) + 1, 20), "yyyy年m月d日")
- .[a2] = Left(.[a2].Value, Application.Find("制表时间:", .[a2].Value) + 4) & s
- End With
- Next
- Application.DisplayAlerts = True
- End Sub
复制代码 |
|