|
本帖最后由 贝贝数码 于 2017-3-9 09:27 编辑
以下代码是论坛jsgj2023老师给我写的,但自己在整理原始数据时,改动了原始数据,所以代码不能对原始数据 达到拆分效果,我的原始数据文件的表头,应该是6行,整理上传成了4行,拆分后,我还得手工变成6行,所以希望哪位老师能帮我注释及修改一下,先谢谢了
Sub Adele()
Dim d As Object
Dim kNum As Long
Dim firstR As Long
Dim endR As Long
Dim yDel As Integer
Dim ws As Worksheet
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For yDel = Sheets.Count To 4 Step -1
Sheets(yDel).Delete
Next yDel
Application.DisplayAlerts = True
Set d = CreateObject("scripting.dictionary")
With Sheets("Sheet1")
r = .[a:a].Find(what:="发票代码").Row
c = .Cells(r, Columns.Count).End(xlToLeft).Column
arr = .Range("a1").CurrentRegion
For x = 5 To UBound(arr)
If arr(x, 10) = "小计" Then
If Not d.exists(arr(x, 10)) Then
d(arr(x, 10)) = x
Else
d(arr(x, 10)) = d(arr(x, 10)) & "," & x
End If
End If
Next x
ar = d.items
For y = 0 To UBound(ar): sr = Split(ar(y), ","): Next y
ReDim er(1 To UBound(sr) + 1)
For y = 0 To UBound(sr): k = k + 1: er(k) = sr(y) * 1: Next y
End With
For x = 1 To UBound(er) Step 18
firstR = er(x) - 1
endR = er(x + 17)
If Err.Number <> 0 Then endR = er(UBound(er))
kNum = kNum + 1
Sheets.Add(after:=Sheets(Sheets.Count)).Name = "拆分" & kNum
Set ws = ActiveSheet
With Sheets("Sheet1")
.Range(.Cells(1, 1), .Cells(r, c)).Copy ws.Range("a1")
.Range(.Cells(firstR, 1), .Cells(endR, c)).Copy ws.Range("a5")
End With
Next x
Sheet1.Select
Application.ScreenUpdating = True
End Sub |
|