VBA - Excel年级成绩表中自动分出各班成绩

Public clas As Integer

Sub 统计年级成绩的班级个数() 'C列数据为班级

Dim Class As New Collection On Error Resume Next

For i = 4 To Cells(Rows.Count, 1).End(xlUp).Row Class.Add Cells(i, 3), Cells(i, 3) If Err.Number <> 0 Then Err.Clear Next i

'循环遍历出各班成绩分薄 For j = 1 To Class.Count clas = Class.Item(j)

Call 输入班级把成绩分班 Next j End Sub

Sub 输入班级把成绩分班()

'sheet1为成绩表

Sheets(1).Activate

Range(\ '按班级筛选

Selection.AutoFilter

Selection.AutoFilter Field:=3, Criteria1:=clas Sheets(1).UsedRange.Select Selection.Copy

'避免添加工作簿时因重名而产生错误 On Error Resume Next

Application.DisplayAlerts = False

Worksheets.Add Count:=1, after:=Worksheets(Worksheets.Count) Sheets(Worksheets.Count).Name = clas

If Err.Number <> 0 Then

Sheets(Worksheets.Count).Delete End If

Application.DisplayAlerts = ture Sheets(Worksheets.Count).Paste

'让sheet1取消自动筛选，否则下次将不能筛选 Sheets(1).Select Selection.AutoFilter End Sub