|
Sub test2() '不用谢的,保留格式,仅参考
Dim p As String, f As String, s As String, ar, br, i As Long, j As Long, Ran As Range
DoApp False
p = ThisWorkbook.Path & "\拆分文件夹"
If Dir(p, vbDirectory) = "" Then MkDir p
br = Range("A1").CurrentRegion.Offset(1).Resize(1)
For j = 1 To UBound(br, 2)
br(1, j) = Columns(j).ColumnWidth
Next
ar = Range(Range("A1"), Cells(Rows.Count, 1).End(xlUp))
For i = 2 To UBound(ar)
If ar(i, 1) = "学号" Then
s = ar(i - 1, 1)
f = p & Application.PathSeparator & s & ".xlsx"
Set Ran = Cells(i, 1).CurrentRegion.Offset(1)
If Dir(f) <> "" Then Kill f
With Workbooks.Add
With .Worksheets(1)
Ran.Copy
.Range("A1").PasteSpecial 13
.Range("A1").PasteSpecial 12
For j = 1 To UBound(br, 2)
.Columns(j).ColumnWidth = br(1, j)
Next
.Name = s
End With
.SaveAs f, 51
.Close
End With
End If
Next
Set Ran = Nothing
DoApp
MsgBox "ok! 文件位于:" & vbCr & p, 64
End Sub
Function DoApp(Optional b As Boolean = True)
With Application
.ScreenUpdating = b
.DisplayAlerts = b
If b = True Then .Calculation = -4105 Else .Calculation = -4135
End With
End Function |
|