|
Sub test()
Application.ScreenUpdating = False
Set d = CreateObject("Scripting.Dictionary")
Set wb1 = ThisWorkbook
For Each Sht In Sheets
arA = Sht.[a1].CurrentRegion
For i = 1 To UBound(arA, 2)
If arA(1, i) = "地区" Then j = i: Exit For
Next
For i = 3 To UBound(arA)
d(arA(i, j)) = ""
Next
Next
k = d.keys: t = d.items
For i = 0 To UBound(k)
Application.SheetsInNewWorkbook = wb1.Sheets.Count
Workbooks.Add
Set wb = ActiveWorkbook
With wb
For x = 1 To wb1.Sheets.Count
arA = wb1.Sheets(x).[a1].CurrentRegion
m = 2
For j = 1 To UBound(arA, 2)
If arA(1, j) = "地区" Then jj = j: Exit For
Next
For j = 3 To UBound(arA)
If arA(j, jj) = k(i) Then
m = m + 1
wb1.Sheets(x).Cells(j, 1).Resize(1, UBound(arA, 2)).Copy .Sheets(x).Cells(m, 1)
wb1.Sheets(x).Cells(1, 1).Resize(2, UBound(arA, 2)).Copy .Sheets(x).[a1]
End If
Next
.Sheets(x).Name = wb1.Sheets(x).Name
.Sheets(x).[a2].CurrentRegion.Borders.LineStyle = 1
For y = 1 To UBound(arA, 2)
.Sheets(x).Columns(y).ColumnWidth = wb1.Sheets(x).Columns(y).ColumnWidth
Next
Next
End With
wb.SaveAs ThisWorkbook.Path & "\" & Split(wb1.Name, ".")(0) & "(" & k(i) & ")" & ".xlsx"
wb.Close
Next
MsgBox "OK"
Application.ScreenUpdating = True
End Sub
|
评分
-
2
查看全部评分
-
|