15

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!

Improve this question

5 Answers 5

Reset to default
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

Improve this answer
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 :)

Improve this answer
0
5

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)
Improve this answer
3
  • 2
    always amazed what you can do with a few lines of python!
    – TimoSolo
    Feb 5, 2018 at 15:27
  • 2
    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, 2018 at 17:34
  • The OP specifically mentioned not to merge all the data into a single sheet.
    – Jeroen
    Jul 23, 2020 at 11:39
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.

Improve this answer
0
  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!

Improve this answer
1
  • Yes, works with Excel 2010.
    – Keks Dose
    Nov 5, 2016 at 9:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .