Take data from another close excel workbook with macro

[serdarvergili]

Hi everyone , firstly english is not my main language sorry for that.I have a main file which is working with 15 different source file.In the main file on A columns datas should be taken from source files on TOTAL sheets on A column.But the problem is a lot of data in TOTAL sheets and they are repeating, so how can I take these data without repeat ? Howewer A column in the main file should be automatic updatable.I tried to make that with excels formulas but it didn't work when the source files close.I think only way to make this situation is macro and I dont know anything about macro.Thanks a lot.

Main File: https://drive.google.com/file/d/1zvF_l4EJhHihd1LkQ02CWr-QY0BB6JaR/view?usp=sharing
One of the Source File: https://drive.google.com/file/d/1UwbrDzco11dKa1RaG0JUu56jri4A7cDY/view?usp=sharing

 

[mumps]

Place this macro in a standard module in your Main workbook and run it from there. Change the source workbook name (in red) to suit your needs. The macro assumes that the destination sheet in Main will always be the first sheet. You can change the number (in blue) to suit your needs. Make sure both workbooks are open.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet
    Set srcWS = Workbooks([COLOR="#FF0000"]"Source File 1.xlsx[/COLOR]").Sheets("TOTAL")
    Set desWS = ThisWorkbook.Sheets([COLOR="#0000FF"]1[/COLOR])
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    srcWS.Range("A3:A" & LastRow).Copy
    desWS.Range("A2").PasteSpecial xlPasteValues
    With desWS
        .Range("A1:A" & .Range("A" & .Rows.Count).End(xlUp).Row).RemoveDuplicates Columns:=1, Header:=xlYes
    End With
    LastRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets([COLOR="#0000FF"]1[/COLOR]).Sort.SortFields.Clear
    Sheets([COLOR="#0000FF"]1[/COLOR]).Sort.SortFields.Add Key _
        :=Range("A2:A" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With Sheets([COLOR="#0000FF"]1[/COLOR]).Sort
        .SetRange Range("A1:H" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub