Option Explicit

Sub Importfind()
'Goes to the folder, finds the csv file, opens it and then does the next step.
ChDir "\\fngn.com\us\Projects\Print Production\Reports\DSG Drop reports"

Application.FindFile
Application.ScreenUpdating = False
Application.DisplayAlerts = False

'The next step separates the package code into two columns, example SCE XE instead of SCEXE which allows for wasy lookup by users.
'Then is copies everything and paste the first 7 columns to the tab called Master. The Master tab has formula's that matches the code SCE to another tab in which I developed a way to get the sponsor name and the provider.
   Columns("C:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("B:B").Select
    Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Columns("A:g").Select
    Selection.Copy
    
    Windows("DSG Drop Reports 2016.xlsm").Activate
    Columns("A:g").Select
    ActiveSheet.Paste
    Range("A1").Select
    Dim row, rwindex As Integer
    row = 0
    rwindex = 5
    Range("A2:G100000").Select
    Selection.Copy
    Sheets("Master").Select
    Range("a1").Select
    Do
       ActiveCell.Offset(1, 0).Activate
             
    Loop Until ActiveCell.Value = ""
 'The next step takes columns A-P and paste them to the Master resulting in:Job No  Sponsor Code    File Name   Date Dropped    Quantity    Status  POID  Provider    Sponsor Code    Sponsor name

    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Sheets("Data").Select
    Application.CutCopyMode = False
    Range("A8").Select
    Rows("2:2").Select
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Add Key:=Range("k2:k100000") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Add Key:=Range("E2:E100000") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("DATA").Sort
        .SetRange Range("A1:k100000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("K1").Select
    Range("A2:G100000").Select
    Selection.ClearContents
    Sheets("Master").Select
    Cells.Select
    ActiveSheet.Range("$A$1:$G$174011").RemoveDuplicates Columns:=4, Header:= _
        xlYes
  'It also de-dupes because some of the records are duplicated. The csv files are a running list that keep adding records, which causes some duplication.
    
   Columns("E:E").Select
    Selection.NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"
  
    
   Application.ScreenUpdating = True
   Application.DisplayAlerts = True
  
'The sponsor package codes tab comes from a table in sharepoint that needs to be updated i/e refreshed so any new data will be there.
    
    Sheets("Sponsor Package Codes").Select
    ActiveWorkbook.refreshall
'The Data tabe is just a sheet that I have the MACRO BUTTONS.
Sheets("DATA").Select
    Range("A1").Select
    

    
        
End Sub