vba_cosco_report

Sub CostcoR()


Dim DISCCO, DESTNCO, DATECO


For i = 1 To Cells(1, Columns.Count).End(xlToLeft).Column

If Cells(1, i) = "EQT_ACTY_LAST_FREE_NAME" Then

Cells(1, i) = "LAST FREE DAY(LFD)"

ElseIf Cells(1, i) = "DISC_VOY_REF" Then

DISCCO = Cells(1, i).Column

ElseIf Cells(1, i) = "DEST_NAME" Then

DESTNCO = Cells(1, i).Column

ElseIf Cells(1, i) = "DISC_VSL_ARRIVAL_DT" Then

DATECO = Cells(1, i).Column

End If

Next i



Dim dic

Set dic = CreateObject("scripting.dictionary")

For i = 2 To Cells(Rows.Count, DESTNCO).End(xlUp).ROW

dic(Cells(i, DESTNCO).Value) = ""

Next i

DestName = dic.Keys


Dim MyBook1, MyBook2, MyBook3 As Workbook

Set MyBook1 = ActiveWorkbook

Application.ScreenUpdating = False

'处理总表




'处理数据并复制

For j = 0 To dic.Count - 1



n = DestName(j)

MyBook1.Sheets("COSTO_IMP2").Cells(1, DESTNCO).AutoFilter Field:=DESTNCO, Criteria1:=n & "*"
MyBook1.Sheets("COSTO_IMP2").Cells(1, DISCCO).AutoFilter Field:=DISCCO, Criteria1:="*PL"

Workbooks.Add

Set MyBook2 = ActiveWorkbook

MyBook2.SaveAs MyBook1.Path & "\" & "Costco - " & n & " - " & Format(Date, "yyyy-mm-dd") & ".xlsx"

Sheets.Add
Sheets.Add


MyBook1.Sheets("COSTO_IMP2").Cells.Copy MyBook2.Sheets(1).Cells

MyBook2.Sheets(1).Cells(1, DATECO).AutoFilter Field:=DATECO, Criteria1:=">" & Date + 1

MyBook2.Sheets(1).Cells.Copy MyBook2.Sheets(2).Cells

MyBook2.Sheets(1).AutoFilterMode = False

MyBook2.Sheets(1).Cells(1, DATECO).AutoFilter Field:=DATECO, Criteria1:="<=" & Date + 1

MyBook2.Sheets(1).Cells.Copy MyBook2.Sheets(3).Cells

MyBook2.Sheet