Merging Workbooks & Worksheets.

Nanogirl21

Active Member
Joined
Nov 19, 2013
Messages
329
Office Version
  1. 365
Platform
  1. Windows
I have 74 Excel workbooks all with a common worksheet named Data. How can I merge all 74 Data worksheets into 1 master workbook and worksheet? The headers (Column A-AZ Row 1) are the same. There are other worksheets in all the files, but I am only interested in merging the Data worksheet. All 74 workbooks are in a folder on my desktop. Any help is appreciated. Thank you.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

johnny51981

Board Regular
Joined
Jun 8, 2015
Messages
245
The best way would be through Power Query. You can bring in all files associated to a single Folder location and then Append (not Merge) them together into a data set.

Edited to Add: I say Append (not Merged) because of it being about Power Query. Merging will bring them together horizontally where Appending will bring them together vertically.
 

Nanogirl21

Active Member
Joined
Nov 19, 2013
Messages
329
Office Version
  1. 365
Platform
  1. Windows
The best way would be through Power Query. You can bring in all files associated to a single Folder location and then Append (not Merge) them together into a data set.

Edited to Add: I say Append (not Merged) because of it being about Power Query. Merging will bring them together horizontally where Appending will bring them together vertically.

Thank you. I've never used Power Query. How do I get that started?
 

johnny51981

Board Regular
Joined
Jun 8, 2015
Messages
245
Thank you. I've never used Power Query. How do I get that started?
In Excel, you can access it in the Data tab and then the Get & Transform Data. Select the Get Data > From File > From Folder

The Power Query Editor will then open. After that, since we can't see your Network/Computer folder structure or files, you will probably need to ask more pointed questions with each step. This forum is a great tool, and googling in general is extremely helpful.
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
7,386
Office Version
  1. 365
Platform
  1. Windows
 

Nanogirl21

Active Member
Joined
Nov 19, 2013
Messages
329
Office Version
  1. 365
Platform
  1. Windows
I am getting Expression.Error: The key did not match any rows in the table when trying to combine and load the datav
 

Nanogirl21

Active Member
Joined
Nov 19, 2013
Messages
329
Office Version
  1. 365
Platform
  1. Windows
Ahhh I figured it out! 1 file was not Macro file type. Thank you all!
 

Forum statistics

Threads
1,186,123
Messages
5,955,973
Members
438,229
Latest member
paulmitchell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top