|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
Sub 分表()
Set sh = Sheets("八总")
With sh
r = .Cells(Rows.Count, 2).End(xlUp).Row
ar = .[a1].Resize(r, 22)
mc = Replace(.Name, "总", "分")
End With
With Sheets("八分")
.UsedRange.Borders.LineStyle = 0
.UsedRange.UnMerge
.UsedRange = Empty
.Select
For i = 3 To UBound(ar) Step 2
m = 0
n = 0
ReDim br(1 To UBound(ar) * 3, 1 To 7)
br(1, 1) = ar(i, 1)
rs = .Cells(Rows.Count, 2).End(xlUp).Row + 2
If rs = 3 Then rs = 1
br(2, 2) = "节次"
If ar(i, 1) <> "" Then
For j = 3 To UBound(ar, 2) Step 5
m = m + 1
y = 2
If m = 1 Then
n = n + 4
For s = j To j + 4
y = y + 1
br(n - 3, y) = ar(2, s)
br(n - 2, y) = ar(1, s)
br(n - 1, y) = ar(i, s)
br(n, y) = ar(i + 1, s)
br(n - 1, 2) = ar(i, 2)
br(n, 2) = ar(i + 1, 2)
Next s
n = n + 1
Else
n = n + 3
br(n - 2, 2) = "节次"
For s = j To j + 4
y = y + 1
br(n - 2, y) = ar(1, s)
br(n - 1, y) = ar(i, s)
br(n, y) = ar(i + 1, s)
br(n - 1, 2) = ar(i, 2)
br(n, 2) = ar(i + 1, 2)
Next s
n = n + 1
End If
Next j
.Cells(rs, 1).Resize(1, 7).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.MergeCells = False
End With
Selection.Merge
.Cells(rs, 1) = "******表"
.Cells(rs + 1, 1).Resize(n - 1, 7) = br
.Cells(rs + 1, 1).Resize(n - 1, 7).Borders.LineStyle = 1
End If
Next i
End With
MsgBox "ok!"
End Sub
|
|