combining data from multiple workbooks with the same sheet name into a new workbook

[Stan101]

I have been searching for an understanding of how to pull data from other workbooks and combine them into another workbooks. I am yet to find much online about my needs.

Example:
I have the following workbooks: Workbook1, Workbook2, workbook3, Workbook4
Each workbook has a sheet named sheet1 all formatted the same way. Sheet1 or workbooks 1 to 3 all have data in columns A to F.

I wish to have the contents of sheet1 in Workbook 1-3 to be populated dynamically into Workbook4's sheet one. Is this possible if I run a macro on opening workbook4?

I would like all of workbook1's contents filling rows starting on row 10 of workbook 4 sheet1. Cell A9 would be populated with the name workbook1. After all the contents of sheet1 in workbook 1 is populated, there will be a blank row on workbook4 sheet1 and then the next workbook sheet 1 would populate starting with that workbook name and then the sheet1 contents. And then the same for the workbook3.

The contents of sheet1 on workbook 1 to three would stop being copied after 2 blank cells in column A.

How would I approach this?

 

[rlv01]

I have been searching for an understanding of how to pull data from other workbooks and combine them into another workbooks. I am yet to find much online about my needs

In fact, there is a HUGE amount of information online on how to do this using VBA. Pulling data from multiple workbooks to put into another workbook is probably one of the most commonly asked questions on this board. Try a search of thread titles using the MrExcel search feature for the search term "multiple workbooks" to see for yourself.

I would like all of workbook1's contents filling rows starting on row 10 of workbook 4 sheet1...

And this is the issue with using macros you find online. The devil is in the details. As you said, you have your own specific detailed requirements, but the examples online for combining data from multiple workbooks to put into another workbook are written to meet someone else's specific detailed requirements. If you have experience with VBA, then those examples you find online can be quite valuable in creating your own macro that meets your specific needs. I've learned a lot over the years by looking at other people's code for problems I was faced with. But if you do not know VBA, and do not want to learn, then your options are more limited:

1. Persuade someone to write a VBA macro for you.
2. Hire someone to write a VBA macro for you.

(FWIW, Mr. Excel has consulting service referrals:

Consulting Services

Are you stuck doing a tedious manual process day after day? This can be automated using VBA macros behind your Excel worksheets. For over twenty years, these Excel consultants work with customers via e-mail or telephone to build a solution.

www.mrexcel.com

)


Sometimes #2 above works here, and you can attract someone's interest in your project. But is also true that a lot of the posts that lay out a project specification for a macro that the poster would like someone to create for them go unanswered for lack of interest, or because to do what the poster wants would required a lot of time and effort. In other words, it is something of a lottery. Some win. Some do not.

I will say this - posters who try to write VBA code themselves, and then post their code when they get stuck have a FAR higher chance of getting help here than people who say "I don't know anything about VBA" and just lay out a coding project specification they want someone to code for them. That's because when it comes to coding problems and getting stuck, we have all been there.

Cheers.

 

[Stan101]

Thank you for your reply. I have in the past posted my own code here for others to use should they so choose. I am certainly no expert but can cobble together what I need.

I have done searching online as I said, which includes this forum and did not find a result I was looking for. I used your recommended sear4ch term and didn't find what I was looking for.

I do not want someone to write code for me. I wanted assistance with an approach. I will continue to muddle my way to a result by myself.

 

[rlv01]

I have done searching online as I said, which includes this forum and did not find a result I was looking for.

I don't really know what to say when someone says this, as I know from direct personal experience that there are many posts that deal with what you say you want do to. There just are.

As far as an approach goes, what you want to do can be broken down into several separate tasks. Many of which are the same things anyone else writing a program like this would have to do. Details obviously vary, but the central theme is quite common:

1. Write code to validate the existence of your target workbook (the one you want to put information in) and/or abort if it does not exist.

2. Write code to get the folder name (the path) containing your multiple source workbooks ( e.g. Workbook1, Workbook2, workbook3, Workbook4)

Where you get the folder name from is a design decision you make. You can hardcode to a variable assignment in the macro, get it from a worksheet cell somewhere, make the user navigate to it from a file dialog, or make the user type it into an InputBox.​

3.Write code to validate that the selected folder exists, and abort if it does not.

4. Write code to open the target workbook.

5. Write code to loop through the workbook files in the folder defined in step #2.

6. As you loop though each source workbook file name, you'll need code to:

a. Open the workbook​

b. Locate the data you want.​

c. Copy the data​

d. Paste the data to a worksheet location in the target workbook that you define​

e. Close the source workbook.​

7. When the workbook looping is complete, save the Target workbook.

8. Close the target workbook.

That's a general outline that you can vary according to your specific needs. You don't need to write it all at once. You can write and test each task separately before combining them. In fact the best approach is to ignore the looping initially. Just write a macro that opens only one source workbook and copies or moves the needed information into your target workbook (basically, task 6). Get that debugged and working first then add code to loop through multiple workbooks. calling that macro for each workbook.