|
- Sub test0()
-
- Dim maxRow As Long, titleRow As Long
-
- titleRow = 3
- maxRow = titleRow + 500
-
- DoApp False
-
- Dim data, i As Long, j As Long, k As Long, rowSize As Long
- Dim strPath As String, strName As String, wks As Worksheet
-
- strPath = ThisWorkbook.Path & Application.PathSeparator & "分簿"
- If Dir(strPath, vbDirectory) = "" Then MkDir strPath
- strPath = strPath & Application.PathSeparator
-
- Set wks = ActiveSheet
- data = wks.Range("A1").CurrentRegion.Value
-
- rowSize = titleRow
- For i = titleRow + 1 To UBound(data)
- rowSize = rowSize + 1
- For j = 1 To UBound(data, 2)
- data(rowSize, j) = data(i, j)
- Next
- If rowSize = maxRow Then
- k = k + 1
- strName = "拆分" & k
- wks.Copy
- With ActiveWorkbook
- With .Worksheets(1)
- .Range("A1").Resize(rowSize, j - 1) = data
- .UsedRange.Offset(, j - 1).Clear
- .UsedRange.Offset(rowSize).Clear
- .DrawingObjects.Delete
- .Name = strName
- End With
- .SaveAs strPath & strName, 51
- .Close
- End With
- rowSize = titleRow
- End If
- Next
-
- If rowSize > titleRow Then
- k = k + 1
- strName = "拆分" & k
- wks.Copy
- With ActiveWorkbook
- With .Worksheets(1)
- .Range("A1").Resize(rowSize, j - 1) = data
- .UsedRange.Offset(, j - 1).Clear
- .UsedRange.Offset(rowSize).Clear
- .DrawingObjects.Delete
- .Name = strName
- End With
- .SaveAs strPath & strName, 51
- .Close
- End With
- End If
-
- Set wks = Nothing
- DoApp
- Beep
- End Sub
- Function DoApp(Optional b As Boolean = True)
- With Application
- .ScreenUpdating = b
- .DisplayAlerts = b
- .Calculation = -b * 30 - 4135
- End With
- End Function
复制代码 |
|