I actually manage to do the job with this code since i don't have to change names :
Sub ImportFiles()
Dim wbNew As Workbook, strPath As String, xWS As Worksheet, xMWS As Worksheet, xTWB As Workbook
Dim xStrAWBName As String, Sh1 As Worksheet, FolderName As String, R As Long, Lr1 As Long
Dim FolderPath As String, fldr As FileDialog, sItem As String, FileName As String, Lr2 As Long
Dim LC As Long
On Error Resume Next
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = Application.DefaultFilePath
If .Show <> -1 Then GoTo NextCode
sItem = .SelectedItems(1)
End With
NextCode:
FolderName = sItem
Set fldr = Nothing
FolderPath = FolderName & "\"
Set xTWB = ThisWorkbook
Set Sh1 = xTWB.Sheets("Data Collector")
FileName = Dir(FolderPath & "*.xls*")
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
Do While FileName <> ""
Workbooks.Open FileName:=FolderPath & FileName, ReadOnly:=True
xStrAWBName = ActiveWorkbook.Name
For Each xWS In ActiveWorkbook.Sheets
Lr1 = xWS.Range("A" & Rows.Count).End(xlUp).Row
R = Application.WorksheetFunction.CountA(xWS.Range("A1:A" & Lr1))
If R > 0 Then
Lr2 = Sh1.Range("A" & Rows.Count).End(xlUp).Row + 1
xWS.Range("A1:A" & Lr1).Copy Sh1.Range("A" & Lr2)
End If
Next xWS
Workbooks(xStrAWBName).Close
FileName = Dir()
Loop
xTWB.SaveAs FileName:="C:\Users\kuser\Desktop\Energy Storage", FileFormat:=xlWorkbookNormal
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
End Sub
