|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
Sub SplitToWsb() 'by ***笨鸟飞不高***
Dim i%, j%, qrz%, xh%, hjh%, cfl%, bth%, r%, k%, m%, hjStr$, tStr$, ScStr$, fName$
Dim rng As Range, rngs As Range, ws As Worksheet, wb As Workbook
Dim arr, tempAr, hjAr, d As Object
Set d = CreateObject("Scripting.Dictionary")
qrz = MsgBox("当前是否选择了拆分总表?", vbYesNo)
If qrz = 7 Then Exit Sub
On Error Resume Next
Set rng = Application.InputBox("请选择带标题行的拆分数据区域", Type:=8)
If Err.Number <> 0 Then Exit Sub
On Error GoTo 0
Application.ScreenUpdating = False
If rng.Count < 2 Then Exit Sub
arr = rng
xh = MsgBox("拆分区域的第一列是否为序号列?", vbYesNo)
hjh = MsgBox("拆分的分表是否要加合计行?", vbYesNo)
If hjh = 6 Then
hjStr = InputBox("请输入合计数据在拆分区域中所对应的列数,如果是多列,以""@""符号分开", "提示")
End If
cfl = Val(InputBox("请输入拆分关键字在拆分区域中所对应的列数", "提示"))
If cfl < LBound(arr, 2) Or cfl > UBound(arr, 2) Then Exit Sub
bth = Val(InputBox("请输入标题行在拆分区域中的行数", "提示"))
If bth > 0 Then Set rngs = Cells(rng(1).Row, rng(1).Column).Resize(bth, UBound(arr, 2))
ScStr = InputBox("拆分输出到工作表还是工作簿 ?", "提示", "工作表")
If ScStr <> "工作表" And ScStr <> "工作簿" Then Exit Sub
Application.DisplayAlerts = False
If ScStr = "工作表" Then
For Each ws In Sheets
If ws.Name <> ActiveSheet.Name Then ws.Delete
Next
End If
If ScStr = "工作簿" Then
qrz = MsgBox("此操作将会覆盖目标文件夹内同名的工作簿!", vbYesNo)
If qrz = 7 Then Exit Sub
End If
For i = bth + 1 To UBound(arr)
If Len(arr(i, cfl)) Then d(arr(i, cfl)) = d(arr(i, cfl)) & "," & i
Next
For i = bth + 1 To UBound(arr)
If d.exists(arr(i, cfl)) Then
If Not d.exists(arr(i, cfl) & "|Sc") Then
r = 0: d(arr(i, cfl) & "|Sc") = "": tStr = d(arr(i, cfl)): tempAr = Split(tStr, ",")
ReDim ScAr(1 To UBound(tempAr) + 1, 1 To UBound(arr, 2))
If hjh = 6 Then ScAr(UBound(ScAr), 1) = "合计"
For k = 1 To UBound(tempAr)
r = r + 1
If xh = 6 Then
ScAr(r, 1) = r
For j = 2 To UBound(arr, 2): ScAr(r, j) = arr(tempAr(k), j): Next
If hjh = 6 Then
hjAr = Split(hjStr, "@")
For m = 0 To UBound(hjAr)
ScAr(UBound(ScAr), Val(hjAr(m))) = ScAr(UBound(ScAr), Val(hjAr(m))) _
+ arr(tempAr(k), Val(hjAr(m)))
Next
End If
Else
For j = 1 To UBound(arr, 2): ScAr(r, j) = arr(tempAr(k), j): Next
If hjh = 6 Then
hjAr = Split(hjStr, "@")
For m = 0 To UBound(hjAr)
ScAr(UBound(ScAr), Val(hjAr(m))) = ScAr(UBound(ScAr), Val(hjAr(m))) _
+ arr(tempAr(k), Val(hjAr(m)))
Next
End If
End If
Next
If ScStr = "工作表" Then
With Worksheets.Add(, Sheets(Sheets.Count))
.Name = Trim(arr(i, cfl))
If bth > 0 Then rngs.Copy .[a1].Resize(bth, UBound(ScAr, 2))
With .Range("A" & bth + 1).Resize(UBound(ScAr), UBound(ScAr, 2))
.Borders.Weight = xlThin
.HorizontalAlignment = xlCenter
.Font.Name = "宋体"
.Font.Size = 10
' .NumberFormatLocal = "@"
.Value = ScAr
End With
End With
ElseIf ScStr = "工作簿" Then
Set wb = Workbooks.Add
If bth > 0 Then rngs.Copy Workbooks(Workbooks.Count).Sheets(1).[a1].Resize(bth, UBound(ScAr, 2))
With Workbooks(Workbooks.Count).Sheets(1).Range("A" & bth + 1).Resize(UBound(ScAr), UBound(ScAr, 2))
.Borders.Weight = xlThin
.HorizontalAlignment = xlCenter
.Font.Name = "宋体"
.Font.Size = 10
' .NumberFormatLocal = "@"
.Value = ScAr
End With
fName = ThisWorkbook.Path & "\" & Trim(arr(i, cfl)) & ".xlsx"
wb.SaveAs fName
wb.Close
End If
End If
End If
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
代码供参考!!! |
评分
-
2
查看全部评分
-
|