|
发表于 2017-8-31 12:12
|
显示全部楼层
本楼为最佳答案
Sub 拆分()
Dim arr, brr(1 To 100000) As Long, x As Long, k As Long, g As Long
arr = Sheets("报检清单").UsedRange
k = 1: brr(1) = Rows.Count: g = Sheets.Count '给一个最大行数,第一次不删除前面数据:确定工作表个数,
For x = 1 To UBound(arr) '判断合计在工作表位置好确定拆分
If arr(x, 4) = "合计" Then k = k + 1: brr(k) = x
Next
For x = 1 To k - 1 '开始拆分,由于不知道怎么完全复制所以复制整个工作表后删除掉多余部分的方法
Sheets("报检清单").Copy after:=Sheets(Sheets.Count) '复制整个工作表放在最后一位
With Sheets(Sheets.Count)
.Name = "报检清单" & x '命名工作表
.Range(brr(x + 1) + 1 & ":" & brr(k) + 2 & "," & brr(1) & ":" & brr(x)).Delete '删除前面后面部分
brr(1) = 4 '确定拆分数据是在哪行开始,以供删除前面无用的
End With
Next
Call 合并
Call 另存为(g + 1)
End Sub
Sub 合并()
Dim arr, brr, x As Long, k As Long
arr = Sheets("认证零件清单").UsedRange: brr = Sheets("非认证零件清单").UsedRange
Sheets("认证零件清单").Copy after:=Sheets(Sheets.Count) '复制整个工作表放在最后一位
With Sheets(Sheets.Count)
.Name = "零件清单" '命名工作表
Sheets("非认证零件清单").Range("4:" & UBound(brr)).Copy .Cells(UBound(arr), 1) ' 粘贴第二个工作表
k = UBound(arr) + UBound(brr) - 1 '设置变量简化后面
.Rows(k - 3 & ":" & k).Insert '插入行
.Cells(k, 5) = brr(UBound(brr), 5) '以下写入后面二行相关数据
.Cells(k, 8) = brr(UBound(brr), 8)
.Cells(k + 1, 5) = "=SUM(R[-" & k & "]C:R[-1]C)/2"
.Cells(k + 1, 8) = "=SUM(R[-" & k & "]C:R[-1]C)/2"
.PageSetup.PrintArea = "$A$1:$K$" & k + 1 '显示打印区域
End With
End Sub
Sub 另存为(yg As Long)
Dim x As Integer, n(), wb As Workbook, m As Workbook
Set m = ActiveWorkbook
ReDim n(0 To Sheets.Count - yg)
For x = yg To Sheets.Count '所以工作表名放入数组
n(x - yg) = Sheets(x).Name
Next x
Sheets(n).Move '移动数组中名称的工作表
Set wb = ActiveWorkbook
If Dir(m.Path & "\" & "清单" & Date & ".xls") = "" Then MsgBox "当前工作表所在路径已有【清单" & Date & "】工作表,请删除或者移动到别的地方": Exit Sub
wb.SaveAs m.Path & "\" & "清单" & Date & ".xlsx" '另存为
wb.Close True '保存工作表
End Sub
|
评分
-
查看全部评分
|