vba_cosco_report_vba end(xlup).column

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.Sheets(1).AutoFilterMode = False
 
MyBook1.Sheets(1).AutoFilterMode = False
 
 
Sheets(1).Cells.ColumnWidth = 30
Sheets(1).Cells.RowHeight = 20
Sheets(1).Cells.EntireColumn.AutoFit
 
Sheets(2).Cells.ColumnWidth = 30
Sheets(2).Cells.RowHeight = 20
Sheets(2).Cells.EntireColumn.AutoFit
 
Sheets(3).Cells.ColumnWidth = 30
Sheets(3).Cells.RowHeight = 20
Sheets(3).Cells.EntireColumn.AutoFit
 
 
 
Sheets(1).Name = n
 
Sheets(2).Name = "Onboard" & " - " & n
 
Sheets(3).Name = "Discharged" & " - " & n
 
MyBook2.Save
 
MyBook2.Close
 
Next j
 
 
Application.ScreenUpdating = True
 
MsgBox "Done"
 
 
 
 
End Sub
 
Sub CostcoRbeifen()
 
 
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
 
For i = 0 To dic.Count - 1
 
MsgBox DestName(i)
 
Next i
 
 
Dim MyBook1, MyBook2, MyBook3 As Workbook
 
Set MyBook1 = ActiveWorkbook
 
With Application.FileDialog(msoFileDialogFilePicker)
    .Title = "Select A File"
    .InitialFileName = "C:\Users\GSC.BFU\Desktop\bOB"
    .AllowMultiSelect = False
    .Filters.Clear
    .Filters.Add "Text File", "*.txt"
    .Filters.Add "EXCEL File", "*.xlsx; *.xls", 1
    .Filters.Add "All File", "*.*", 1
    If .Show Then
        .ButtonName = "Select Me"
        Set ipath = .SelectedItems
    End If
End With
 
If IsEmpty(ipath) Then Exit Sub
ipath = ipath(1)
 
Set MyBook3 = Workbooks.Open(ipath)
MyBook3.Sheets("DISCHARGED").Copy After:=MyBook1.Sheets(1)
MyBook3.Sheets("ONBOARD").Copy After:=MyBook1.Sheets(1)
MyBook3.Close
 
 
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
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.Sheets(1).AutoFilterMode = False
 
MyBook1.Sheets(1).AutoFilterMode = False
 
MyBook1.Sheets("DISCHARGED").Copy MyBook2.Sheets(1)
 
MyBook1.Sheets("ONBOARD").Copy MyBook2.Sheets(1)
 
 
 
For i = 1 To Sheets("DISCHARGED").Cells(2, Columns.Count).End(xlToLeft).Column
 
If Sheets(5).Rows(1).Find(Sheets("DISCHARGED").Cells(2, i)) <> "" Then
 
Sheets(5).Columns(Sheets(5).Rows(1).Find(Sheets("DISCHARGED").Cells(2, i)).Column).Copy Sheets("DISCHARGED").Columns(i)
 
End If
 
Next i
 
 
For i = 1 To Sheets("ONBOARD").Cells(2, Columns.Count).End(xlToLeft).Column
 
If Sheets(4).Rows(1).Find(Sheets("ONBOARD").Cells(2, i)) <> "" Then
 
Sheets(4).Columns(Sheets(4).Rows(1).Find(Sheets("ONBOARD").Cells(2, i)).Column).Copy Sheets("ONBOARD").Columns(i)
 
End If
 
Next i
 
Sheets(1).Cells.ColumnWidth = 30
Sheets(1).Cells.RowHeight = 20
Sheets(1).Cells.EntireColumn.AutoFit
 
o = Sheets(1).Cells(Rows.Count, 1).End(xlUp).ROW
p = Sheets(1).Cells(1, Columns.Count).End(xlToLeft).Column
 
   Sheets(1).[a1].Resize(o, p).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
 
Sheets(2).Cells.ColumnWidth = 30
Sheets(2).Cells.RowHeight = 20
Sheets(2).Cells.EntireColumn.AutoFit
 
Sheets(2).Select
 
o = Sheets(2).Cells(Rows.Count, 2).End(xlUp).ROW
p = Sheets(2).Cells(1, Columns.Count).End(xlToLeft).Column
 
   Sheets(2).[a1].Resize(o, p).Select
   
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
 
Sheets(1).Name = "Onboard Vessel" & " - " & n
Sheets(2).Name = "Discharged" & " - " & n
 
 
MyBook2.Save
 
MyBook2.Close
 
Next j
 
 
Application.ScreenUpdating = True
 
MsgBox "Done"
 
End Sub