|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
我写的速度比较慢,保留格式再加上序号,拆分这几个需要将近二十秒,你试试
Sub 拷贝()
Dim wk As Workbook
Dim sht As Worksheet, sh As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set d = CreateObject("scripting.dictionary")
Set sht = Sheets("那丽镇")
arr = sht.UsedRange
For i = 5 To UBound(arr)
d(arr(i, 4)) = ""
Next i
ReDim brr(1 To UBound(arr), 1 To UBound(arr, 2))
For Each dk In d.keys
For i = 5 To UBound(arr)
If arr(i, 4) = dk Then
k = k + 1
For j = 1 To UBound(arr, 2)
brr(k, j) = arr(i, j)
Next j
End If
Next i
Set wk = Workbooks.Add
wk.Sheets(1).Name = sht.Name & "-" & dk
Set sh = wk.Sheets(1)
sht.Rows("1:" & k).Copy sh.Range("a1")
sht.Columns("a:q").Copy sh.Range("a1")
sh.UsedRange.Offset(4).ClearContents
sh.UsedRange.Offset(k + 4).Clear
sh.Range("a5").Resize(k, UBound(brr, 2)) = brr
ReDim crr(1 To k, 1 To 1)
For xh = 1 To k
crr(xh, 1) = xh
Next xh
sh.Range("a5").Resize(k, 1) = crr
wk.SaveAs ThisWorkbook.Path & "\" & dk & ".xlsx"
wk.Close False
k = 0
Next
Application.ScreenUpdating = True
Application.DisplayAlerts = True
MsgBox "拆分完毕"
End Sub |
评分
-
1
查看全部评分
-
|