|
参与一下。。。
- Sub ykcbf() '//2024.5.8
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Dim t: t = Timer
- Dim d As Object, rng As Range
- Set d = CreateObject("scripting.dictionary")
- Set ws = ThisWorkbook
- Set sh = ThisWorkbook.Sheets("统计表")
- p = ThisWorkbook.Path & "\成品"
- For Each sht In ws.Sheets
- If sht.Name <> sh.Name Then
- arr = sht.UsedRange
- c = Application.WorksheetFunction.Match("班级", sht.Rows(1), 0)
- For i = 2 To UBound(arr)
- s = arr(i, c)
- If s <> "" Then d(s) = ""
- Next i
- End If
- Next sht
- For Each k In d.keys
- ws.Sheets.Copy
- Set wb = ActiveWorkbook
- For Each sht In wb.Sheets
- If sht.Name <> sh.Name Then
- With sht
- arr = .UsedRange
- c = Application.WorksheetFunction.Match("班级", sht.Rows(1), 0)
- For i = 2 To UBound(arr)
- If arr(i, c) <> k Then
- If rng Is Nothing Then
- Set rng = .Rows(i)
- Else
- Set rng = Union(rng, .Rows(i))
- End If
- End If
- Next i
- If Not rng Is Nothing Then rng.Delete
- Set rng = Nothing
- End With
- End If
- Next
- With wb.Sheets(sh.Name)
- .[b1] = k
- .[b7] = k
- .DrawingObjects.Delete
- End With
- wb.SaveAs p & k & "班", 56
- wb.Close
- Next k
- Application.ScreenUpdating = True
- Application.DisplayAlerts = True
- MsgBox "拆分完毕,共用时: " & Format(Timer - t, "0.000秒"), , "提示"
- End Sub
复制代码
|
评分
-
1
查看全部评分
-
|