Combine multiple Excel workbooks into a single workbook

Question

I am new to Visual Basic. I can use Excel 2010 or Excel 2013 for this task.

I have dozens of books with data on the first sheet of each. For example, One.xlsx, Two.xlsx, Three.xlsx, Four.xlsx contain information about their corresponding sheet1.

I need information on Sheet1 from each book, which will be combined into one book with sheets whose names are indicated in the file name of the source book. So, for example, comb.xlsx will have 4 sheets with the name One, Two, Three, Four. In each case, all information about the base sheets should be copied and combined into a new Workbook, as shown below.

• Format i need

I found this Macro / Add-In online, which brings me closer to what I need using open files, optionally added.

http://www.excelbee.com/merge-excel-sheets-2010-2007-2013#close

The open file add-in allows me to group various worksheets into one workbook. However, the tabs are not called by the name of the source file.

• The correct union of sheets, but the wrong names of worksheets.

So far, all basic books will be in the same folder. The ability to browse and select files would be nice if it ever changed, but if it is too complicated, it will simply indicate the directory path in Visual Basic code. Since the final combined result is likely to be a new book, the name of the new book is not so important. It could be called comb.xlsx, for example.

Answer 1

The task is performed as follows.

Option Explicit Private Sub CommandButton1_Click() Dim directory As String, fileName As String, sheet As Worksheet, total As Integer Dim WrdArray() As String Application.ScreenUpdating = False Application.DisplayAlerts = False directory = "c:\test\" fileName = Dir(directory & "*.xl??") Do While fileName <> "" Workbooks.Open (directory & fileName) WrdArray() = Split(fileName, ".") For Each sheet In Workbooks(fileName).Worksheets Workbooks(fileName).ActiveSheet.Name = WrdArray(0) total = Workbooks("import-sheets.xlsm").Worksheets.Count Workbooks(fileName).Worksheets(sheet.Name).Copy after:=Workbooks("import-sheets.xlsm").Worksheets(total) GoTo exitFor: Next sheet exitFor: Workbooks(fileName).Close fileName = Dir() Loop Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub 

Answer 2

In Excel, press Alt+F11 , this will open the Excel VBA editor.

The article http://www.excel-spreadsheet.com/vba/debugging.htm explains some of the basics of using it.

Module1 has two short routines, opensheets and merge , containing ~ 50 lines of code.

Use F1 with the cursor in words you don’t understand to find out what that means.

Once you understand what the code is doing, you can adapt it to your needs.