11

I have a few dozen excel files which are all of the same format (i.e. 4 worksheets per Excel file). I need to combine all the files into 1 master file which must have just 2 of the 4 worksheets. The corresponding worksheets from each Excel file are named exactly the same as are the column headers.

While each file is structured the same, the information within sheet 1 and 2 (for example) is different. So it can’t be combined into one file with everything in one sheet!

I've never used VBA before and I'm wondering where I might start this task!

3

Since you wanted them on one sheet, I think the above isn't what you were looking for.

I use Microsoft Access to merge data. Especially, if different sheets have a similar identifier (part number/contact person/ect).

You create a "table" for each spreadsheet to be merged You create a "query" which pulls the desired columns to one sheet

7

Please see the Consolidate Worksheets Wizard add-in for Excel

The add-in has several merge modes, one of them does exactly what you need.

Please see this link for a detailed description of the mode (how to combine sheets with the same name to one)

The add-in is a shareware, but it has a 15-day fully-funtional trial version (download button at the top of the page), so you can merge thousands of your workbooks for free :)

4

Today I came across this link RDBMerge, Excel Merge Add-in for Excel for Windows, which I think will serve the purpose.That is a free macro based tool.

4

Here is a solution in python.

import glob
import pandas as pd

path = "<path to files>"
file_identifier = "*.xlsx"

all_data = pd.DataFrame()
for f in glob.glob(path + "/*" + file_identifier):
    df = pd.read_excel(f)
    all_data = all_data.append(df,ignore_index=True)
  • always amazed what you can do with a few lines of python! – TimoSolo Feb 5 '18 at 15:27
  • 1
    to write out the file, use something like this: writer = pd.ExcelWriter('merged.xlsx', engine='xlsxwriter') \n all_data.to_excel(writer, sheet_name='Sheet1') \n writer.save() – TimoSolo Feb 5 '18 at 17:34
1
  1. Open all of the Excel workbooks in the same instance of Excel.
    • You might have to click the internal "restore" button to see the individual workbooks.
  2. Select all sheets you want to move by Ctrl+Clicking on the worksheet tabs.
  3. Right click on one of the selected tabs, and choose Move or Copy...
  4. In the dialog that pops up, select the destination workbook (your "Master" workbook) and then choose where to insert them.
    • The (move to end) option is likely what you want, but you can always reorder them later.
    • Choose Create a copy if you do not want the sheets to be removed from the first workbook
  5. Click OK.

The selected worksheets will be moved or copied from the original workbook into your "Master" workbook. Just close the source wookbook and do it again with the next one, until you've collected all the worksheets you care about into one large wookbook. Make sure to save!

  • Yes, works with Excel 2010. – Keks Dose Nov 5 '16 at 9:28

protected by Raystafarian May 20 '16 at 18:09

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.