Sub NewCXL()
' Windows(Format(Date - 1, "mmddyy") & "_CHINA_OO_DETAILS").Activate
' Worksheets(Format(Date - 1, "mmddyy") & "_CHINA_OO_DETAILS").Select
Application.DisplayAlerts = False
fname = ActiveWindow.Caption
forms = ActiveSheet.Name
Set ChF = Workbooks(fname).Sheets(forms)
Range(Cells(2, 1), Cells(60000, 200)).Sort Key1:=Range("D1")
Columns("X:X").Insert Shift:=xlToRight
Range("X2:X" & [Q1048576].End(xlUp).Row).FormulaR1C1 = "=RC[-1]/RC[-2]"
Columns("X:X").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("X2:X" & [Q1048576].End(xlUp).Row).NumberFormatLocal = "0%"
Rows("1:1").AutoFilter
Rows("1:1").Select
Selection.AutoFilter Field:=11, Criteria1:="=HARDLINES", Operator:=xlOr, _
Criteria2:="=HOME"
Selection.AutoFilter Field:=24, Criteria1:=">=80%", Operator:=xlAnd
Rows("1:1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Worksheets.Add
ActiveSheet.Name = "80%"
Range("A1").Select
ActiveSheet.Paste
Sheets("80%").Select
Sheets("80%").Copy
Windows(Format(Date - 1, "mmddyy") & "_CHINA_OO_DETAILS").Activate
Sheets(Format(Date - 1, "mmddyy") & "_CHINA_OO_DETAILS").Select
Rows(1).Select
Selection.AutoFilter Field:=11, Criteria1:="=HARDLINES", Operator:=xlOr, _
Criteria2:="=HOME"
Selection.AutoFilter Field:=24, Criteria1:=">=80%", Operator:=xlAnd
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.EntireRow.Delete
Rows("1:1").AutoFilter
Rows("1:1").AutoFilter
Rows(1).Select
Selection.AutoFilter Field:=24, Criteria1:=">=90%", Operator:=xlAnd
Rows("1:1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Book1").Activate
Worksheets.Add
ActiveSheet.Name = "90%"
Range("A1").Select
ActiveSheet.Paste
Windows(Format(Date - 1, "mmddyy") & "_CHINA_OO_DETAILS").Activate
Sheets(Format(Date - 1, "mmddyy") & "_CHINA_OO_DETAILS").Select
Rows(1).Select
Selection.AutoFilter Field:=24, Criteria1:=">=90%", Operator:=xlAnd
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.EntireRow.Delete
Rows("1:1").AutoFilter
Rows("1:1").AutoFilter
Rows(1).Select
Selection.AutoFilter Field:=1, Criteria1:=">=21", Operator:=xlAnd
Rows("1:1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Book1").Activate
Worksheets.Add
ActiveSheet.Name = "21"
Range("A1").Select
ActiveSheet.Paste
Sheets("80%").Select
Rows("1:1").AutoFilter
Columns("A:A").Insert Shift:=xlToRight
Range("A1") = "COMMENT"
Range("A2:A" & [R1048576].End(xlUp).Row).Value = "Cxl 80% rcvd" & "(cwa " & Format(Date, "mm/dd)")
Sheets("90%").Select
Columns("A:A").Insert Shift:=xlToRight
Range("A1") = "COMMENT"
Range("A2:A" & [R1048576].End(xlUp).Row).Value = "Cxl 90% rcvd" & "(cwa " & Format(Date, "mm/dd)")
Sheets("21").Select
Columns("A:A").Insert Shift:=xlToRight
Range("A1") = "COMMENT"
Range("A2:A" & [R1048576].End(xlUp).Row).Value = "Cxl 21 days" & "(cwa " & Format(Date, "mm/dd)")
Sheets("90%").Select
Range("A2:BI" & [R1048576].End(xlUp).Row).SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("80%").Select
r1 = [Q1048576].End(xlUp).Row
Range("a" & r1 + 1).Select
ActiveSheet.Paste
Sheets("21").Select
Range("A2:BI" & [R1048576].End(xlUp).Row).SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("80%").Select
r1 = [Q1048576].End(xlUp).Row
Range("a" & r1 + 1).Select
ActiveSheet.Paste
Worksheets("80%").Select
ActiveSheet.Name = "ALL"
Sheets("90%").Select
ActiveWindow.SelectedSheets.Delete
Sheets("21").Select
ActiveWindow.SelectedSheets.Delete
Sheets("ALL").Select
Columns("R:R").Cut
Columns("A:A").Insert Shift:=xlToRight
Columns("AU:AU").Cut
Columns("B:B").Insert Shift:=xlToLeft
Columns("D:H").Select
Columns("D:H").Insert Shift:=xlToRight
Range("D1") = "NAME"
Range("E1") = "COMPLETE"
Range("F1") = "Cancellation level"
Range("A1:A" & [A1048576].End(xlUp).Row).Copy
' advance filter
Worksheets.Add
ActiveSheet.Name = "Advan"
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1:A" & [A1048576].End(xlUp).Row).NumberFormatLocal = "0000000000"
Range("A1:A" & [A1048576].End(xlUp).Row).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
ActiveWorkbook.Save
Range("A1:A" & [A1048576].End(xlUp).Row).Copy
Sheets.Add.Name = "POs"
Range("A1").Select
ActiveSheet.Paste
Range(Cells(2, 1), Cells(60000, 2)).Sort Key1:=Range("A1")
Range("B2:B" & [A1048576].End(xlUp).Row) = "=TEXT(RC[-1],""0000000000"")"
Range("C2:C" & [A1048576].End(xlUp).Row) = "=""'""&RC[-1]&""',"""
End Sub
Sub NewCXL2()
ActiveSheet.Name = "Lookup"
Columns("C:G").Insert Shift:=xlToRight
Columns("M:M").Cut
Columns("G:G").Insert Shift:=xlToLeft
Columns("Y:Y").Insert Shift:=xlToRight
Range("Y2:Y" & [Q1048576].End(xlUp).Row).FormulaR1C1 = "=RC[-1]/RC[-2]"
Columns("Y:Y").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("Y2:Y" & [Q1048576].End(xlUp).Row).NumberFormatLocal = "0%"
Range("H2:H" & [B1048576].End(xlUp).Row).Formula = "=B2&G2"
Columns("H:H").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Sheets("ALL").Select
Range("G2:G" & [B1048576].End(xlUp).Row).Formula = "=A2&B2"
Range("H2:H" & [A1048576].End(xlUp).Row).Formula = "=VLOOKUP(G2,LOOKUP!H:Y,18,0)"
Columns("G:H").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Rows("1:1").AutoFilter
Rows("1:1").AutoFilter
Rows(1).Select
Selection.AutoFilter Field:=8, Criteria1:=">=1", Operator:=xlAnd
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
ActiveSheet.ShowAllData
'COUNTIF
Columns("A:A").Copy
Worksheets.Add
ActiveSheet.Name = "COUNTIF"
Range("A1").Select
ActiveSheet.Paste
Range("B2:B" & [A1048576].End(xlUp).Row).Formula = "=COUNTIF(A2:A2000,A2)"
Range("C2:C" & [A1048576].End(xlUp).Row).Formula = "=A2&B2"
Range("B2:C" & [A1048576].End(xlUp).Row).Copy
Range("B2:C" & [A1048576].End(xlUp).Row).PasteSpecial Paste:=xlPasteValues
Sheets("LOOKUP").Select
Range("C2:C" & [B1048576].End(xlUp).Row).Formula = "=Countif(B2:B2000,B2)"
Range("D2:D" & [B1048576].End(xlUp).Row).Formula = "=B2&C2"
Range("D2:D" & [B1048576].End(xlUp).Row).Select
Range("E2:E" & [B1048576].End(xlUp).Row).Formula = "=VLOOKUP(D:D,COUNTIF!C:C,1,0)"
Range("C2:E" & [B1048576].End(xlUp).Row).Copy
Range("C2:E" & [B1048576].End(xlUp).Row).PasteSpecial Paste:=xlPasteValues
Rows("1:1").AutoFilter
Selection.AutoFilter Field:=5, Criteria1:="<>#N/A", Operator:=xlAnd
r = [A1048576].End(xlUp).Row
If r <> 2 Then
Range("F2:F" & [A1048576].End(xlUp).Row) = "header level"
End If
ActiveSheet.ShowAllData
Rows("1:1").Select
Selection.AutoFilter Field:=6, Criteria1:="<>header level", Operator:=xlAnd
r = [A1048576].End(xlUp).Row
If r <> 2 Then
Range("F2:F" & [A1048576].End(xlUp).Row) = "line level"
End If
Sheets("ALL").Select
Range("F2:F" & [A1048576].End(xlUp).Row).Formula = "=VLOOKUP(A2,Lookup!B:F,5,0)"
Range("F2:F" & [A1048576].End(xlUp).Row).Copy
Range("F2:F" & [A1048576].End(xlUp).Row).PasteSpecial Paste:=xlPasteValues
Columns("A:A").NumberFormatLocal = "0000000000"
Cells.Select
Selection.Sort Key1:=Range("F2"), Order1:=xlAscending, Key2:=Range("A2") _
, Order2:=xlAscending, Key3:=Range("B2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
SortMethod:=xlPinYin, DataOption1:=xlSortNormal, DataOption2:= _
xlSortNormal, DataOption3:=xlSortNormal
e = 2
Do Until Cells(e, 2) = Empty
If Left(Cells(e, 3), 11) = "Cxl 21 days" Then
Rows(e).Select
Selection.Interior.ColorIndex = 6
Else
End If
e = e + 1
Loop
Columns("G:H").Select
Columns("G:H").Delete
ActiveWorkbook.SaveAs Filename:="C:\Cxl\New CXL % " & Format(Date, "mm.dd.yy") & ".xlsx"
End Sub
本文来源：https://www.2haoxitong.net/k/doc/82ad2024ed630b1c59eeb54a.html
文档为doc格式