|
Sub 宏3()
Dim dic
Set dic = CreateObject("scripting.dictionary")
Set dic1 = CreateObject("scripting.dictionary")
arr = Sheet1.Range("a1").CurrentRegion
brr = Sheet2.Range("a1").CurrentRegion
For a = 2 To UBound(arr)
If Not dic.exists(arr(a, 5)) Then
dic(arr(a, 5)) = arr(a, 5) & "_" & arr(a, 2)
End If
Next
For a = 2 To UBound(brr)
If Not dic1.exists(brr(a, 4)) Then
dic1(brr(a, 4)) = brr(a, 4) & "_" & brr(a, 1)
End If
Next
For a = 1 To dic.Count
ActiveSheet.Range("$A$1:$E$" & UBound(arr)).AutoFilter Field:=5, Criteria1:=dic.keys()(a - 1)
Sheets("小额").Select
ActiveSheet.Range("$A$1:$D$" & UBound(brr)).AutoFilter Field:=4, Criteria1:=dic.keys()(a - 1)
Workbooks.Add
ChDir "C:\Users\Administrator\Desktop"
ActiveWorkbook.SaveAs Filename:="C:\Users\Administrator\Desktop\" & dic.items()(a - 1) & ".xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Windows("模板.xls").Activate
Range("A1:D" & Sheet2.[a60000].End(xlUp).Row).Copy
Windows(dic.items()(a - 1) & ".xlsx").Activate
Sheets("Sheet2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "小额"
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "大额"
Windows("模板.xls").Activate
Sheets("大额").Select
Range("A1:E" & Sheet1.[a60000].End(xlUp).Row).Copy
Windows(dic.items()(a - 1) & ".xlsx").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveWorkbook.Save
ActiveWindow.Close
Next
End Sub
|
|