Combining Data from Multiple Excel Files into One Master Sheet

Hi

I have spent some time searching for help/code online to do this but can't find anything useful. I really hope you can help.

I need to combine data on 100+ Excel files (all in one folder) with only 1 worksheet in each file into one single master sheet on another file. These Excel files are like forms with data that I want to combine in a single sheet. So all these Excel files will have the same worksheet name and location of data to be combined will be the same throughout, just that the file name would be different.

I have attached some sample files and hope it will help. If there's a primer that's useful to get me started, great! If there's existing code that requires minor editing, that's even better! I am also open to other ideas on how to automate this.

Thank you in advance!

Glen

If there's existing code that requires minor editing

Does that mean that you prefer to look at it and change it to your needs?
If so, go through this code and change to your requirements.

Please Login or Register  to view this content.

Ok, let me give this a try and will report back. Thanks!

Ok, it didn't take very long for me to get stuck. I understand the code except the .Range part.

How do I do the following?

• On the first file, copy a single cell from source to a single cell to destination, and on subsequent files, it will be copy to the next row
• Do the same for a range of data

How do you know if it will be the first file?
Does that file have a different range to copy (one cell)? Which cell?
Do you know the name of that file?
The code example pastes into the first empty cell in column A. Is that where that one cell value (or text) will be pasted?
What are the ranges you want to copy in all the other files?

Try this on a copy of your workbook.
Code goes in the "Master" workbook.

Please Login or Register  to view this content.

This works perfectly! I have a couple more questions and this should wrap it up.

1. If I want to include a drop-down box for users to select resource type (highlighted in red on the attached), how would you code it to copy numbers according to the resource type selected and only when a resource type is selected? I will still maintain three fixed ranges on the master data sheet with all three resources.
2. This is not related to #1. How do I include calculations when copying numbers from source data to master data? For example, I want to multiply the "units" row (shown in orange) by "price" in cell D4 and paste it in the destination range. I am guessing I need to use Select Case but not 100% sure.

Thank you very much, I really appreciate your help!

Instead of copying only certain rows, would it be OK to copy everything and hiding columns of the Resources you don't want to see?
Can the multiplication be done in the "Master Data" sheet after everything has been copied over?

For the multiplication part, change this

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

Hi glennchung
I had another look at your latest attachment where you have the dropdowns in cells A7, A9 and A11 in the Workbook where the information is copied from.
Should the dropdown not be in the Master Workbook? After all, you run the code from The Master Workbook.

The dropdowns will be on the source file and the Master Workbook will just have fixed ranges for data to be copied to. So depending on what the dropdown is on the source file, it will be copied to a fixed location in the Master Workbook. Say if it's Resource 1 on the source file, then copy that specific range of 10 cells to a range in the Master Workbook destinated for Resource 1 (for example, starting from column N, and this won't change). But the same range on the source file can be Resource 1, or Resource 2, or Resource 3 since there's a dropdown to the left. However, all resources will have their fixed columns in the Master Workbook. I hope this will clarify it. Thank you!

Originally Posted by jolivanes

Instead of copying only certain rows, would it be OK to copy everything and hiding columns of the Resources you don't want to see?
Can the multiplication be done in the "Master Data" sheet after everything has been copied over?

I thought about it but I am trying to keep the Master Workbook small since there will be a lot of source files to pull data in. Thanks for the update!

I thought about it but I am trying to keep the Master Workbook small since there will be a lot of source files to pull data in.

This is not going to make your file bigger.

How many files could there be where you copy from? In your last post you indicated that there could be a lot.
In all these files, I assume that Cells B7:K7 is Resource 1, B9:K9 is Resource 2 and B11:K11 is Resource 3.
In Cells A7, A9 and A11 you have a choice of "Select Resource Type", Resource 1", "Resource 2" and "Resource 3"
So what needs to be done if Cell A7 has "Resource 3" (should be Resource 1 IMHO), Cell A9 has "Resource 3" (should be Resource 2 IMHO) and in Cell A11 has "Resource 1" (should be Resource 3 IMHO)?
And what if "Select Resource Type" has been selected? Nothing should happen I assume.
Also, before you run the code in your "Master" you need to go and open all the workbooks, select whatever you want in your drop downs, save the workbooks and go to your "Master" to run the code.
As you indicated before that there could be a lot of files, this will end up to be very time consuming and defeats the purpose of using code to speed up the operation.
Nevermind the real possibility of getting wrong information when the selection in the drop downs is questionable.
You can run everything from your "Master" if it is set-up properly.
Let us know how you want to proceed.

Try this on a copy of your workbook.
If you have a lot of files to be copied from, it might be advised to start the code at lunch time.
It might be done when you come back from lunch!!!!
All kidding aside, until someone comes with a better and faster solution, this should do for the time being.
Good luck

Please Login or Register  to view this content.

Thanks jolivanes, appreciate the effort! I will give it a try later today. Thanks.