|
Sub kk()
Dim dic As Object, arr(), brr(), arrTemp(), arrCount()
Set dic = CreateObject("scripting.dictionary")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each sht In Worksheets
If sht.CodeName <> "Sheet1" Then
sht.Delete
End If
Next
With Sheet1
arr = .Range("a1").CurrentRegion
ReDim arrTemp(1 To UBound(arr), 1 To 7)
For i = 2 To UBound(arr)
x = Year(arr(i, 4))
If dic.exists(x) = False Then
n = n + 1
dic(x) = n
ReDim Preserve brr(1 To n)
ReDim Preserve arrCount(1 To n)
brr(n) = arrTemp
End If
k = dic(x)
arrCount(k) = arrCount(k) + 1
brr(k)(arrCount(k), 1) = arr(i, 1)
brr(k)(arrCount(k), 2) = arr(i, 2)
brr(k)(arrCount(k), 3) = arr(i, 3)
brr(k)(arrCount(k), 4) = arr(i, 4)
brr(k)(arrCount(k), 5) = arr(i, 5)
brr(k)(arrCount(k), 6) = arr(i, 6)
brr(k)(arrCount(k), 7) = arr(i, 7)
Next i
End With
For i = 1 To n
Set sht = Worksheets.Add(, Worksheets(Worksheets.Count))
With sht
Sheet1.Rows(1).Copy .Range("a1")
.Range("C:C", "E:E").NumberFormatLocal = "@"
.Range("D:D").NumberFormatLocal = "yyyy/m/d"
.Range("a2").Resize(arrCount(i), 7) = brr(i)
.Range("a2").Resize(arrCount(i), 7).Columns.AutoFit
.Range("a2").Resize(arrCount(i), 7).Borders.LineStyle = 1
.Name = dic.keys()(i - 1)
End With
Next i
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Set dic = Nothing
End Sub |
评分
-
查看全部评分
|