|
记录生成拆分汇总.rar
(27.38 KB, 下载次数: 42)
- Sub 生成记录()
- Dim arr, brr(), r&, i&, n&
- arr = Sheets("测试总表").[a1].CurrentRegion
- ReDim brr(1 To 1000, 1 To 12)
- For i = 2 To UBound(arr)
- If arr(i, 11) <> "" Then
- n = n + 1
- brr(n, 1) = i
- For j = 2 To 12
- brr(n, j) = arr(i, j)
- Next
- End If
- Next
- If n > 0 Then
- r = Range("a65536").End(3).Row
- ' Range("b2:b" & r) = ""
- Range("k2:k" & r) = ""
- With Sheets("生成记录")
- .Cells.Clear
- Rows("1:2").Copy '复制测试总表第1行至第2行
- .Rows("1:2").PasteSpecial xlPasteAll '值与格式一起粘贴到新建表的1至4行
- .Rows(1).PasteSpecial xlPasteColumnWidths '选择性粘贴列宽
- .Columns(13).Delete
- .Rows("2").Copy
- .Rows(3 & ":" & 50).PasteSpecial xlPasteFormats '选择性粘贴第2行格式
- .Range("a2").Resize(n, 12) = brr
- .Range("a1").Resize(n + 1, 12).Borders.LineStyle = xlContinuous ' 1添加整体边框线实线'xlContinuous 1
-
- End With
- End If
- MsgBox "OK!"
- End Sub
- Sub 折分工作表()
- Dim arr, brr(), d, k, r&, i&, n&, j&
- Set d = CreateObject("scripting.dictionary")
- Application.ScreenUpdating = False '关闭屏幕刷新
- Application.DisplayAlerts = False
- arr = Sheets("测试总表").[a1].CurrentRegion
- For i = 2 To UBound(arr)
- d(arr(i, 3)) = ""
- Next
- k = d.keys
- 删除分表
- For Each k In d.keys
- ReDim brr(1 To 5000, 1 To 13)
- For i = 2 To UBound(arr)
- If arr(i, 3) = k Then
- n = n + 1
- brr(n, 1) = n
- For j = 2 To 13
- brr(n, j) = arr(i, j)
- Next
- End If
- Next
- Sheets.Add After:=Sheets(Sheets.Count)
- Sheets(Sheets.Count).Name = k
- With ActiveSheet
- Sheets("测试总表").Rows(1).Copy '复制测试表第1行
- .Rows(1).PasteSpecial xlPasteAll '值与格式一起粘贴到新建表的1行
- .Rows(1).PasteSpecial xlPasteColumnWidths '选择性粘贴列宽
- .Range("a2").Resize(n, 13) = brr '正表赋值
- Sheets("测试总表").Rows(2 & ":" & n + 1).Copy
- .Rows(2 & ":" & n + 1).PasteSpecial xlPasteFormats '选择性粘贴第2行格式
- .Range("a2").Resize(n, 13).Borders.LineStyle = xlContinuous
- End With
- n = 0
- Next
- Application.ScreenUpdating = True '关闭屏幕刷新
- Application.DisplayAlerts = True
- MsgBox "OK!"
- End Sub
- Sub 删除分表()
- Application.DisplayAlerts = False
- For Each Sh In Sheets
- If Sh.Name <> "测试总表" And Sh.Name <> "生成记录" And Sh.Name <> "参数设置" Then Sh.Delete
- Next
- Application.DisplayAlerts = True
- End Sub
复制代码
|
评分
-
1
查看全部评分
-
|