|
拆分表中BCD及目录都好了还差个A表
总是搞不定,有哪位大侠帮忙修改下代码
以便可以用
在线等谢谢了
Sub test()
Dim r%, i%
Dim arr, brr
Dim wb As Workbook
Dim ws As Worksheet
Dim d As Object
Dim d1 As Object
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.SheetsInNewWorkbook = 5
Set d = CreateObject("scripting.dictionary")
Set d1 = CreateObject("scripting.dictionary")
d1("B") = Array("b3:l5", 11, 6)
d1("C") = Array("B2:r5", 17, 6)
d1("D") = Array("B2:z4", 25, 5)
For Each ws In Worksheets
flg = Left(ws.Name, 1)
If flg Like "[B-D]" Then
brr = d1(flg)
With ws
r = .Cells(.Rows.Count, 2).End(xlUp).Row
arr = .Range("b1:b" & r)
For i = brr(2) To UBound(arr)
If Not d.exists(arr(i, 1)) Then
Set d(arr(i, 1)) = CreateObject("scripting.dictionary")
End If
If Not d(arr(i, 1)).exists(ws.Name) Then
Set d(arr(i, 1))(ws.Name) = .Range(brr(0))
End If
Set d(arr(i, 1))(ws.Name) = Union(d(arr(i, 1))(ws.Name), .Cells(i, 2).Resize(1, brr(1)))
Next
End With
End If
Next
For Each aa In d.keys
Set wb = Workbooks.Add
With wb
ThisWorkbook.Worksheets("目录及报表说明").UsedRange.Copy .Worksheets(1).Range("c2")
Worksheets(1).Name = "目录及报表说明"
m = 2
For Each bb In d(aa).keys
With Worksheets(m)
.Name = bb
d(aa)(bb).Copy .Range("b3")
End With
m = m + 1
Next
.SaveAs Filename:=ThisWorkbook.Path & "\" & "财务数据_" & aa & ".xls"
.Close False
End With
Next
End Sub
|
|