|
- Sub tt()
- Dim thwb As Workbook, wb As Workbook, sh As Worksheet
- Set thwb = ThisWorkbook
- Set wb = Workbooks.Add
- thwb.Worksheets(Array("学校汇总表", "英语统计", "科学统计", "思品统计", "语数统计", "及格率", "优秀率")).Copy _
- after:=wb.Sheets(wb.Sheets.Count) '工作表复制
- For Each sh In wb.Worksheets
- If sh.Name = "学校汇总表" Then '学校汇总表排序
- r = sh.[a65536].End(3).Row
- sh.Range("b4:aj" & r).Sort key1:=sh.[aj4]
- ElseIf sh.Name Like "*统计" Then '英语统计,科学统计。。。。排序
- r = sh.[a65536].End(3).Row
- For i = 1 To r
- If sh.Cells(i, 1) = "序号" Then
- r1 = sh.Cells(i, 1).End(xlDown).Row
- sh.Range(sh.Cells(i + 1, 2), sh.Cells(r1, "H")).Sort key1:=sh.Cells(i + 1, "G")
- End If
- Next
- ElseIf sh.Name Like "*率" Then '及格率、优秀率排序
- r = sh.[a65536].End(3).Row
- For i = 1 To 9 Step 2
- sh.Range(sh.Cells(3, i), sh.Cells(r, i + 1)).Sort key1:=sh.Cells(3, i + 1), order1:=xlDescending
- Next
- Else '删除多余工作表
- Application.DisplayAlerts = False
- sh.Delete
- Application.DisplayAlerts = True
- End If
- Next
- wb.SaveAs thwb.Path & "\排序.xls" '保存退出
- wb.Close
- End Sub
复制代码 |
|