|
- Sub 复制成绩()
- Application.Calculation = xlCalculationManual
- Application.ScreenUpdating = False
- Dim Filename, wb As Workbook, Sh As Worksheet
- Filename = Dir(ThisWorkbook.Path & "\各校成绩单\*.xls")
- Set d = CreateObject("scripting.dictionary")
-
- For Each Sh In ThisWorkbook.Worksheets '清空原各年级表中数据
- If Sh.Name Like "*年级" Then Sh.[a2:i10000].ClearContents
- Next
-
- Do While Filename <> ""
- fn = ThisWorkbook.Path & "\各校成绩单" & Filename
- Set wb = Workbooks.Open(fn)
- With wb.Worksheets(1)
- arr = .[a1].CurrentRegion
- For i = 3 To UBound(arr)
- nj = arr(i, 2) '年级所在列
- If nj Like "*年*" Then
- nj = Mid(nj, InStr(nj, "年") - 1, 1) & "年级" '年级
- If Not d.exists(nj) Then
- Set d(nj) = .Cells(i, 1).Resize(1, 9)
- Else
- Set d(nj) = Union(d(nj), .Cells(i, 1).Resize(1, 9))
- End If
- End If
- Next
- End With
- For Each Sh In ThisWorkbook.Worksheets
- If Sh.Name Like "*年级" Then
- If d.exists(Sh.Name) Then
- r = Sh.[a65536].End(3).Row + 1
- d(Sh.Name).Copy Sh.Cells(r, 1)
- End If
- End If
- Next
- wb.Close False
- d.RemoveAll
- Filename = Dir
- Loop
- Set Sh = Nothing
- Application.Calculation = xlCalculationAutomatic
- Application.ScreenUpdating = True
- End Sub
复制代码 |
|