A fairly common mistake. Instead of x1ToLeft it should be xlToLeft. But there are many more errors in the code.
Try the revised macro:
Sub Open_All_Excel_Files_in_a_Folder_and_Copy_Data_1()
Dim ShTarget As Worksheet
Dim Sheet_Name As String
Dim File_Dialog As FileDialog
Dim File_Path As String
Dim File_Name As String
Dim lColL As Long
Dim lColS As Long
Dim wbFile As Workbook
Dim i As Long
' Select file location and open excel worksbooks to copy "Sheet1" into a seperate workbook to collect all data from the other files'
Sheet_Name = "Sheet1"
Set ShTarget = ThisWorkbook.Worksheets(Sheet_Name)
Set File_Dialog = Application.FileDialog(msoFileDialogFolderPicker)
File_Dialog.AllowMultiSelect = False
File_Dialog.Title = "Select the Excel Files"
If File_Dialog.Show <> -1 Then
Exit Sub
End If
File_Path = File_Dialog.SelectedItems(1)
If Right(File_Path, 1) <> Application.PathSeparator Then
File_Path = File_Path & Application.PathSeparator
End If
File_Name = Dir(File_Path & "*.xls*")
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
lColL = 0
Do While File_Name <> ""
Set wbFile = Workbooks.Open(Filename:=File_Path & File_Name)
wbFile.Worksheets(Sheet_Name).UsedRange.Copy
lColL = lColL + 1
lColS = lColL
ShTarget.Cells(1, lColL).PasteSpecial Paste:=xlPasteAll
lColL = lColL + wbFile.Worksheets(Sheet_Name).UsedRange.Columns.Count
File_Name = Dir()
wbFile.Close False
'Removing columns on row 2 based on the header'
For i = lColL To lColS Step -1
If ShTarget.Cells(2, i).Value = "MX840B_CH_2" Or ShTarget.Cells(2, i).Value = "MX840B_CH_3" Then
ShTarget.Columns(i).Delete
lColL = lColL - 1
End If
Next i
Loop
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub
Artik
Bookmarks