When you want to use this scenario, you just only need to click Scenario button to choose the name of the scenario you need on step2 wizard. Then the specified worksheets have been merged into a new workbook, and the worksheets are named with the workbook name before original worksheet name.A new worksheet named Kutools is also generated at the front of all the worksheets at the same time, which lists the workbooks with detailed information of each worksheets.Have you ever been stuck when you have to combine multiple workbooks into a single workbook in Excel?The most terrible thing is that the workbook you need to combine contains multiple worksheets.
I have dozens of workbooks with data on the first worksheet of each.The ability to browse and select the files would be nice if this ever changes but if that is too difficult, just indicating the directory path in the Visual Basic code would work. Name = Wrd Array(0) total = Workbooks("import-sheets.xlsm"). As far as the resultant combined output probably ought to be a new workbook, the filename of the new workbook isn't that important. Option Explicit Private Sub Command Button1_Click() Dim directory As String, file Name As String, sheet As Worksheet, total As Integer Dim Wrd Array() As String Application. Display Alerts = False directory = "c:\test\" file Name = Dir(directory & "*.xl?? Open (directory & file Name) Wrd Array() = Split(file Name, ".") For Each sheet In Workbooks(file Name). It actually plays an important role if you have to deal with a large amount of Excel file in your daily work. On step2 of wizard, all the opened workbooks and worksheets are listed in the list boxes, click Add button to add other files or folders that you want to combine.Now, let's see how to get this function work in combining multiple workbooks. There is a dialog pops out to remind you that the active workbook cannnot to be combined, click OK to continue. In the Combine Worksheets wizard, select Combine multiple worksheets from workbooks into one workbook. Then in the poppding dialog to select the workbooks you want to combine, click Open to go back to Combine dialog, and you can check the workbooks or sheets you need to combine. Then click Finish button and choose a folder to merge and save the workbooks, and then a dialog pops out to ask you if to open the merged workbook.So for example would have 4 sheets named One, Two, Three, Four.In every case all information on the underlying worksheets should be copied and combined in the new Workbook as shown below. And then specify the location of the merged worksheets. Then it combines all worksheets of opened workbooks into a single workbook.See screenshots: Fortunately, for rookies and new hands of Microsoft Excel, there are some available tools to help you quickly combine hundreds of workbooks into one.See screenshot: Tip: you will be asked if you want to save this scenario.If you want to save this scenario, please click Yes, and enter the name of the scenario, (see following screenshots), if you don’t want to save this scenario, please click No.