Merging Workbooks & Worksheets.

[Nanogirl21]

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.

 

[johnny51981]

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]

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]

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]

 

[Nanogirl21]

I am getting Expression.Error: The key did not match any rows in the table when trying to combine and load the datav

 

[Nanogirl21]

Ahhh I figured it out! 1 file was not Macro file type. Thank you all!