|
本帖最后由 武林长风 于 2015-8-2 09:26 编辑
请教vba大师,点击按钮提取每个年级的百人榜到百人榜工作表。见附件。
把表1作为辅助表 - Sub Macro1()
- Application.ScreenUpdating = False
- s = 1: Sheet8.UsedRange.Clear
- Sheets(1).Activate
- For i = 2 To 7
- ActiveSheet.UsedRange.Clear
- Sheets(i).UsedRange.Copy ActiveSheet.[a1]
- If i < 4 Then
- [g1] = "总分": [h1] = "名次"
- [G2] = "=SUM(E2:F2)"
- [G2].AutoFill Range("g2:g" & Range("a65536").End(xlUp).Row)
- [h2] = "=RANK(G2,G:G,0)"
- [h2].AutoFill Range("h2:h" & Range("a65536").End(xlUp).Row)
- Range("a1").CurrentRegion.Sort [G2], Order1:=xlDescending, Header:=xlGuess
- Range("a1").CurrentRegion = Range("a1").CurrentRegion.Value
- n = [g:g].Find(Cells(101, "g"), searchdirection:=xlPrevious).Row
- Range("a1:h" & n).Copy Sheet8.Cells(s, 1)
- Else
- [j1] = "总分": [k1] = "名次"
- [J2] = "=SUM(E2:I2)"
- [J2].AutoFill Range("J2:J" & Range("a65536").End(xlUp).Row)
- [K2] = "=RANK(J2,J:J,0)"
- [K2].AutoFill Range("K2:K" & Range("a65536").End(xlUp).Row)
- Range("a1").CurrentRegion.Sort [J2], Order1:=xlDescending, Header:=xlGuess
- Range("a1").CurrentRegion = Range("a1").CurrentRegion.Value
- n = [J:J].Find(Cells(101, "j"), searchdirection:=xlPrevious).Row
- Range("a1:k" & n).Copy Sheet8.Cells(s, 1)
- End If
- Sheet8.Cells(s, 1).CurrentRegion.Borders.LineStyle = xlContinuous
- s = Sheet8.Range("a65536").End(xlUp).Row + 2
- Next
- ActiveSheet.UsedRange.Clear
- Sheet8.Activate
- Application.ScreenUpdating = True
- End Sub
复制代码
|
|