Copy Range from multiple Workbooks in folder, to Summary Workbook also in same folder?

**xlyfe** · 08-23-2018, 10:30 AM

Hello,

I have a folder with an unlimited amount of workbooks in it which each contain a range of data.
This data resides on the same sheet for each workbook. This sheet in each workbook is labeled "Summary".
On the "Summary" sheet, the data needed resides from cell ("A3:G?").
The data begins on "A3", and ends on column "G" one row up from the last row with data in it.

What I would like to do it grab this data from each Workbook in this folder, within that range, and paste it into another Workbook in that same folder.
We can call this new Workbook the "Master Summary" workbook.
I need this newly copied data in the "Master Summary" workbook to begin it's pasting at cell ("A3").

I'm imagining that I will assign this macro to a button which will execute the extracting of the data from each workbook into the "Master Summary" workbook.

Also, need to keep in mind that new Workbooks will be added to this folder continuously, so this code would need to be able to constantly include newly added Workbooks.

Anybody have any ideas on how I can start this?

Thank you for your help and advice in advance!

-Mike in Cali

**Olly** · 08-23-2018, 11:34 AM

This is so easy to achieve with Power Query (Get & Transform Data). Something like:

Combine Files:

Please Login or Register  to view this content.

fnEachFileContents:

Please Login or Register  to view this content.

**xlyfe** · 08-23-2018, 01:20 PM

Originally Posted by Olly

This is so easy to achieve with Power Query (Get & Transform Data). Something like:

Combine Files:

Please Login or Register  to view this content.

fnEachFileContents:

Please Login or Register  to view this content.

So I'm not too fluent with this Power Query Editor. I have two questions.

1) Am I creating 2 separate queries with this code of yours?

2) The first one, "Combine Files", is giving me an (Expression.SyntaxError: Token Comma expected.). And it's highlighted the (#"Get Files Contents") as the root of the error. I'm attempting to troubleshoot this at the moment, but being that I'm very new to Power Query Editor, I'm having some difficulty.

Thank you so much for you help Olly.

**xlyfe** · 08-23-2018, 01:47 PM

I'm trying to figure out this Power Query thing and I just don't see how it's going to be easier for me to manage as VBA would be. Currently I'm stumped.

**xlyfe** · 08-23-2018, 05:49 PM

Yeah. I'm still not figuring out this Power Query.

Anybody else out there know how I can achieve what I'm trying to do with VBA?

Any advice or guidance is greatly appreciated.

**Olly** · 08-24-2018, 04:07 AM

See attached workbook for a working example.

Change the path reference from "C:\Temp\Source" to your required path. If there are other files in that folder, as well as the ones you wish to combine, then you may need to apply a filter, to choose only the excel files to combine, before you apply the function.

It's worth taking a little time to understand how Power Query works - for performance, reliability and simplicity of data handling, it's way easier than VBA.

In this case, to update your data, simply refresh the query.

**xlyfe** · 08-24-2018, 12:24 PM

Originally Posted by Olly

See attached workbook for a working example.

Change the path reference from "C:\Temp\Source" to your required path. If there are other files in that folder, as well as the ones you wish to combine, then you may need to apply a filter, to choose only the excel files to combine, before you apply the function.

It's worth taking a little time to understand how Power Query works - for performance, reliability and simplicity of data handling, it's way easier than VBA.

In this case, to update your data, simply refresh the query.

Hello Olly,

Thank you sooooo much for introducing me to this part of Excel. After studying up on it for the past few hours and putting it to the test, I can't believe that I have lived without it for this long.

You are correct that it is very easy to use once given proper time to learn it.

I solved my problem that I was asking for this thread with Power Query. I did not use your custom code tho. Instead I built my own queries from scratch. I'm still blown away by how easy it was.

Thank you again for the guidance. I can't wait to find another project to use this on.

**Olly** · 08-24-2018, 01:22 PM

That's a great response - I'm delighted you took the time to introduce yourself to Power Query. It really is one of the best things to happen with Excel in many years!

Once you are comfortable with shaping your data using Power Query, then spend some time learning about building a data model using Power Pivot...

Copy Range from multiple Workbooks in folder, to Summary Workbook also in same folder?

LinkBack

Thread Tools

Rate This Thread

Display

Copy Range from multiple Workbooks in folder, to Summary Workbook also in same folder?

Re: Copy Range from multiple Workbooks in folder, to Summary Workbook also in same folder?

Re: Copy Range from multiple Workbooks in folder, to Summary Workbook also in same folder?

Re: Copy Range from multiple Workbooks in folder, to Summary Workbook also in same folder?

Re: Copy Range from multiple Workbooks in folder, to Summary Workbook also in same folder?

Re: Copy Range from multiple Workbooks in folder, to Summary Workbook also in same folder?

Re: Copy Range from multiple Workbooks in folder, to Summary Workbook also in same folder?

Re: Copy Range from multiple Workbooks in folder, to Summary Workbook also in same folder?

Thread Information

Users Browsing this Thread

Similar Threads

Copy a Range from each workbook in a folder to another folder

Copy a Range from each workbook in a folder to another folder

Copy a Range from each workbook in a folder to another folder

Moving data from monthly workbooks into summary workbook in same folder

Need to copy filtered rows from multiple workbooks in a folder to an existing workbook

copy & paste data from multiple workbooks to new workbook in a folder

Make a summary workbook from a folder of workbooks

Bookmarks

Bookmarks

Posting Permissions