Combining many files into one with a tab for each file

[e119687]

I have a folder with a number of xlsx files inside. In a single spreadsheet, I need to create a tab for each file (tab name based on the file name) and then the data from that file copy and pasted into its respective tab.

Is this something that can be done with a macro or something else?

I have attached a file showing what I am trying to achieve (Combined Data File.xlsx) and two representative files (2607B R.xlsx and 2610B R.xlsx) that would be copied into its given tab.

Thanks in advance if you can help me solve this problem.

 

[Marc L]

Where should be located the VBA procedure ? Within the 'Combined' workbook or ?​

​

 

[e119687]

Hi Marc, thank you for responding. I'm sorry I did not get back to you sooner. I believe the VBA should be stored in the Personal workbook for use at any time by me.

 

[Marc L]

According to your attachment a VBA demonstration for starters :​

Sub Demo1()
  Const P = "D:\Tests4Noobs\e119687\", C = P & "Combined Data .xlsb"
    Dim F$, Wb As Workbook
        F = Dir$(P & "*.xlsx"):  If F = "" Then Beep: Exit Sub
    For Each Wb In Workbooks
        If Wb.FullName = C Then Wb.Close False: Set Wb = Nothing: Exit For
    Next
   With Application
       .DisplayAlerts = False
       .ScreenUpdating = False
   Do
   With Workbooks.Open(P & F, 0)
        If Wb Is Nothing Then .Sheets(1).Copy: Set Wb = ActiveWorkbook Else .Sheets(1).Copy , Wb.Sheets(Wb.Sheets.Count)
       .Close
   End With
        ActiveSheet.Name = Left(F, Len(F) - 5)
        F = Dir$
   Loop Until F = ""
       Wb.SaveAs C, 50
      .Speech.Speak "Done", True
      .DisplayAlerts = True
      .ScreenUpdating = True
   End With
       Set Wb = Nothing
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​