Summarize Data from Multiple Worksheets

[zjrhockey]

Hello,

I am having some trouble finding a way to efficiently generate reports from multiple workbooks located int he same folder. All of the workbooks have the same formatting and the data being pulled is located in the same position in each workbook. My folder structure is a main folder called recipe weeks where each weeks recipes will be stored, that folder contains many folder labeled week 100, 101, 103, etc. Each of these folders contain multiple recipes that I need to summarize. Ideally I would have 1 workbook that has a tab dedicated to each recipe week and the data from each recipe summarized in that tab. I have attached a blank version of what each workbook looks like and I need to summarize the Variance datafor each recipe (located in H5).

If anyone has some suggestions that would be great! Thanks!

 

[Luke M]

Check out some of the merging examples here: http://www.rondebruin.nl/win/section3.htm

One of those should be able to copy/paste the data together the way you want it. You could even use Ron's add-in, if you don't want to mess with the code yourself.

 

[Chihiro]

Not sure what the final output should look like. But you can use PowerQuery or MS Query to pull the data from each workbook and put it into new sheet (combine it with Macro to automate connection creation).