Macro to copy cells from several files into one file

Hi guys,

I don’t know much about VBA and wanted to see if by providing me an example with explanation as to what each code does whether I can learn the basics for my needs.

My goal is to combine data from multiple different Excel files into a single new file. Please see the attached files. This is what I am trying to achieve - copy the data from all individual Excel files contained in a given folder, let’s call it “C:\Combo” folder, Paste the values of that data into a single Excel files.

Example:
1. Look in file “Data File 1,” sheet “Full IO”
2. Copy A20 into cell G2 of “Summary File” sheet “Primary”
3. Copy C20:C49 into G3:G32 of “Summary File” sheet “Primary”
4. Copy M20:M49 into G3:G32 of “Summary File” sheet “Secondary”
5. Move over into the next column and copy the same information from the next Excel file (Data File 2) in the given folder and repeat the loop until all the files are copied

I know that there are a few example online, but the code is not properly explained and I can’t quickly figure out how it works. Seeing a working example like this should help me truly understand how the coding works.

I would appreciate your help with the example of the code. I am trying to change the way we manage data in our lab since almost no one is utilizing the power of VBA.

Thanks.

These steps

2. Copy A20 into cell G2 of “Summary File” sheet “Primary”
3. Copy C20:C49 into G3:G32 of “Summary File” sheet “Primary”
4. Copy M20:M49 into G3:G32 of “Summary File” sheet “Secondary”

do not match your Summary file. The data does not match at all. Can you please check again.

I'm afraid your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

I reckon it's more that your question is a little vague and the sample files don't help if they don't match your request
as an idea

Please Login or Register  to view this content.

Well, if something is not clear, I can explain. I tried to understand the code you wrote. So it seems you're using dim to define variables. I don't understand everything though. For example, you have defined an array of files, but my goal is not to define them, but to use all excel files consecutively in a given folder path. If I have over 100 files, creating an array would not be feasible if I have to name each file.

Let's forget about the attachments. The core of my question is this:

Let's say I have a file called "DataFile1.xlsx" that has a sheet called "DataSheet." I want to create a macro that will copy cells A12:A15 from that sheet into another file called "Summary.xlsx" in the sheet of "SummarySheet" and cells B13:B16.

What would be the standalone code for that?

Now, I want to take that same code and create a loop that will do the exact same procedure for every file in a folder called "DataFolder" and copy them into the summary sheet in the next column, the next column, .... until the files run out.

PS: I have submitted a request to delete all the other cross posts.

the code I gave shows how to get the values from one sheet to another-all you need to change is the array part so that it loops through a folder, or create a function to return an array of file names from a folder. I'm on a deadline at the moment so I can't help with that part right now but the Dir function will help you ;-)

you need quotes around the file names in the array statement

Please Login or Register  to view this content.