Sub NewCXL.docx

Sub NewCXL.docx

《Sub NewCXL.docx》由会员分享，可在线阅读，更多相关《Sub NewCXL.docx（8页珍藏版）》请在冰豆网上搜索。

SubNewCXL

SubNewCXL（）

'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

SetChF=Workbooks（fname）.Sheets（forms）

Range（Cells（2,1）,Cells（60000,200））.SortKey1:

=Range（"D1"）

Columns（"X:

X"）.InsertShift:

=xlToRight

Range（"X2:

X"&[Q1048576].End（xlUp）.Row）.FormulaR1C1="=RC[-1]/RC[-2]"

Columns（"X:

X"）.Select

Selection.Copy

Selection.PasteSpecialPaste:

=xlPasteValues

Range（"X2:

X"&[Q1048576].End（xlUp）.Row）.NumberFormatLocal="0%"

Rows（"1:

1"）.AutoFilter

Rows（"1:

1"）.Select

Selection.AutoFilterField:

=11,Criteria1:

="=HARDLINES",Operator:

=xlOr,_

Criteria2:

="=HOME"

Selection.AutoFilterField:

=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.AutoFilterField:

=11,Criteria1:

="=HARDLINES",Operator:

=xlOr,_

Criteria2:

="=HOME"

Selection.AutoFilterField:

=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.AutoFilterField:

=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.AutoFilterField:

=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.AutoFilterField:

=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"）.InsertShift:

=xlToRight

Range（"A1"）="COMMENT"

Range（"A2:

A"&[R1048576].End（xlUp）.Row）.Value="Cxl80%rcvd"&"（cwa"&Format（Date,"mm/dd）"）

Sheets（"90%"）.Select

Columns（"A:

A"）.InsertShift:

=xlToRight

Range（"A1"）="COMMENT"

Range（"A2:

A"&[R1048576].End（xlUp）.Row）.Value="Cxl90%rcvd"&"（cwa"&Format（Date,"mm/dd）"）

Sheets（"21"）.Select

Columns（"A:

A"）.InsertShift:

=xlToRight

Range（"A1"）="COMMENT"

Range（"A2:

A"&[R1048576].End（xlUp）.Row）.Value="Cxl21days"&"（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"）.InsertShift:

=xlToRight

Columns（"AU:

AU"）.Cut

Columns（"B:

B"）.InsertShift:

=xlToLeft

Columns（"D:

H"）.Select

Columns（"D:

H"）.InsertShift:

=xlToRight

Range（"D1"）="NAME"

Range（"E1"）="COMPLETE"

Range（"F1"）="Cancellationlevel"

Range（"A1:

A"&[A1048576].End（xlUp）.Row）.Copy

'advancefilter

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）.AdvancedFilterAction:

=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））.SortKey1:

=Range（"A1"）

Range（"B2:

B"&[A1048576].End（xlUp）.Row）="=TEXT（RC[-1],""0000000000""）"

Range（"C2:

C"&[A1048576].End（xlUp）.Row）="=""'""&RC[-1]&""',"""

EndSub

SubNewCXL2（）

ActiveSheet.Name="Lookup"

Columns（"C:

G"）.InsertShift:

=xlToRight

Columns（"M:

M"）.Cut

Columns（"G:

G"）.InsertShift:

=xlToLeft

Columns（"Y:

Y"）.InsertShift:

=xlToRight

Range（"Y2:

Y"&[Q1048576].End（xlUp）.R