Hello,

I have excel-files that are in a chronological order inside of a folder. I want to extract every Sheet1 from the individual excel-files and combine into one single excel-file with a single sheet. So far, I have successfully achieved that with the code. However, there is a lot of unnecessary data that follows in the copy & paste process, and I?m only interested in one column. Im trying to remove these "unnecessary" columns at the final step in my code on the sheet with all the data is pasted for the summarized excel-file. However, I have tried multiple approaches and it won?t work. The error I have is " Run-time error '1004'.. Please help!

Here is the code:

Sub Open_All_Excel_Files_in_a_Folder_and_Copy_Data()


' Select file location and open excel worksbooks to copy "Sheet1" into a seperate workbook to collect all data from the other files'

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Sheet_Name = "Sheet1"
Set New_Workbook = ThisWorkbook

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) & ""
File_Name = Dir(File_Path & "*.xls*")

ActiveColumn = 0
Do While File_Name <> ""
Set File = Workbooks.Open(Filename:=File_Path & File_Name)
File.Worksheets(Sheet_Name).UsedRange.Copy
ActiveColumn = ActiveColumn + 1
New_Workbook.Worksheets(Sheet_Name).Cells(1, ActiveColumn).PasteSpecial Paste:=xlPasteAll
ActiveColumn = ActiveColumn + File.Worksheets(1).UsedRange.Columns.Count
File_Name = Dir()

Loop


'Removing columns on row 2 based on the header'

last_column = Worksheets("Sheet1").Cells(2, Columns.Count).End(x1ToLeft).Column
For i = 1 To last_column
If Cells(2, i).Value = "MX840B_CH_2" Then
Columns(i).Delete
End If

If Cells(2, i).Value = "MX840B_CH_3" Then
Columns(i).Delete
End If


Next

End Sub