Multiple workbook into a Masterwork book

[ranjith08]

I'm trying to get the information from multiple workbook that follow the same template but with different information on each one into one master workbook but I'm having trouble as the multiple workbook will constantly be changing as more support calls are made, I tried to write the code below but it will only work the workbook as it is not when it's updated in the future. I have attached the sample files hoping someone can help me out with this. Thank you in advance

[COLOR=#000000][FONT=&quot]Sub LoopThroughDirectory()[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]Dim MyFile As String[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]Dim erow[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]Dim Filepath As String[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]Filepath = "C:\Desktop\CRM\"[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]MyFile = Dir(Filepath)[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]Do While Len(MyFile) > 0[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    If MyFile = "Draft template for CRM.xls*" Then[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    Exit Sub[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    End If[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    Workbooks.Open (Filepath & MyFile)[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    Range("*:*").Copy[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    ActiveWorkbook.Close[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 4))[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    MyFile = Dir[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]Loop[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]End Sub[/FONT][/COLOR]

SAMPLE WORKBOOK DOWNLOAD: https://we.tl/t-YwQhj3pBWw