Copy a Specific Range of Data from multiple workbooks that are closed

[paula.mccall]

Hello,

As a novice of vb, I am in the “struggle mode of understanding.”
I have a task that I desperately need to complete, but I am having problems and I am hoping you can help.
I have multiple workbooks/worksheets (reports) – all are formatted the same. I only need a specific range of data from each worksheet, within each workbook (A2:M2). I need to copy the data from each worksheet/workbook (all files located in the same folder), into a Separate workbook (file named: “Master_A,” worksheet named: “Master”). I need to do this without opening the workbooks, AND, I need to do this as a running report (not removing previous, or already existing data), but instead adding all new data when prompted to run the macro.
**note: Once the data has been copied from the workbook & each worksheet, to the master, the workbook is moved into a different folder.

**note: The data to be copies are based from formulas within the worksheets, so I would need to paste special (maybe as text?)
**note: The range of data I need to copy from each worksheet, within each workbook is, A2:M2
**note: The Master worksheet is set up with the same headers (A through M)
**Note: Each workbook has a unique Title, with uniquely named worksheets
**note: Workbooks may contain 1, 2, 3.... worksheets (it varies)

**note: I even tried to make an "Appendix" tab for each workbook, where the data would go to from each sheet - but, more errors



I have tried multiple "Sub CopyRange()" codes, but cant seem to get the script to run with error after error.

Please Login or Register  to view this content.

Any help you can offer will be greatly appreciated.

[vcoolio]

Hello Paula,

Try the following code:-

Please Login or Register  to view this content.

The code should collect all data from Columns A:M in all source worksheets in the source workbooks and paste it into the "Master" worksheet of your "Master" workbook. You'll just need to add your file path to the code. It will also place the source workbook and source worksheet names in Column N.

**note: Once the data has been copied from the workbook & each worksheet, to the master, the workbook is moved into a different folder.

The code does not do this as I'm not sure if you want the source workbooks or just the Master workbook moved to a new folder.
For a start, test the above first to at least see if the copy/paste works.

I hope that this helps.

Cheerio,
vcoolio.

P.S. If you want the source workbook/worksheet names placed in Column N of the Master worksheet, then add a heading in N1 (e.g. Source Wb & Wksht).

[Greg M]

Hi there,

I need to do this without opening the workbooks

If you really need to avoid opening the workbooks it gets a bit (lots?) more complicated - possible, but not so easy!

Regards,

Greg M

[Olly]

Why not use Power Query?

Something like:

Please Login or Register  to view this content.

[AliGW]

@Paula

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. As you are new here, I have added them for you this time.)

[paula.mccall]

I cannot thank you enough Vcoolio!
The code worked perfect! And, I greatly appreciate the extra side notes - helps me to understand and learn.

Kindest Regards,
Paula

[paula.mccall]

Hello Greg,

Thank you so much for providing this option and information. I have to admit, I will have to practice and learn a bit more about the power query, as it does seem to be a good tool. I really appreciate your time and help! I'm sure I will have more questions as I dive into learning about power query .

Kindest Regards,
Paula

[paula.mccall]

"Solved"

**I apologize for not using tags. Thank you for the correction.

[vcoolio]

You're welcome Paula.
I'm glad to have been able to assist and thanks for the rep.

Cheerio,
vcoolio.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[paula.mccall]

sorry - one more question: if the path folder is shared (such as on a one drive), does this same code work? I created a folder to my hard drive and ran the code and everything worked great. but, when i uploaded it to the one drive and changed the path to that specific folder I received an error message.
thank you again for all your help!

Kindest Regards,
Paula

[vcoolio]

Good day Paula,

I recently had similar (plus other) problems with OneDrive and I ended up breaking all links and uninstalling it. I'm not big on file sharing so uninstalling it doesn't bother me.

However, you may find this thread to be of good help. It should help you resolve this issue:-

https://www.myonlinetraininghub.com/...le-on-onedrive

These two sentences from Philip Treacy may be of particular interest:-

As OneDrive doesn't support VBA. Neither trusted locations nor signed certificates apply.

So, you can store a macro enabled workbook on OneDrive, but if you want to use the macros, you'll need to download it/open it on your desktop.

I hope that this helps.

Cheerio,
vcoolio.

[paula.mccall]

Thank you again vcoolio. Even though I may be running into "snags" here and there, I am learning, so I will take it all with a positive experience .

Can I bother you with another question:

Is there a way to use the code you provided, which does work amazingly well, to also work with copying the B2 row if the files i need to copy from are also .xlms?

Kindest Regards,
Paula

[vcoolio]

Good day Paula,

.....files i need to copy from are also .xlms?

I assume that you mean xlsm.

I can think of three ways just off the top of my head:-

1) Copy the code in post #2, paste it beneath the original code (in the same module) but change this line:-

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

then change the code's title to, say, PaulaMc2 (can't have two codes named exactly the same) and place this new code name just below
'Loop' in the original code. Once the loops are completed for the original code looking for the 'xlsx' extension, the second code will kick in looking for any 'xlsm' extensions.

2) We could re-hash the original code as follows to also find the 'xlsm' extension:-

Please Login or Register  to view this content.

3) Use Power Query as Olly suggests in post #4. Power Query may be a much faster option than option(2) as that code may take a while to execute depending on the quantity of source files that need to be accessed.
Perhaps Olly may like to come back and give you further guidance here. I've only ever glossed over Power Query

I hope that this helps.

Cheerio,
vcoolio.

[vcoolio]

Hello again Paula,

Something else that just dawned on me that may work for you:-

Please Login or Register  to view this content.

This is the same code as the one in post #2 except for the minor change shown in red font. We're just using the 'xls' extension using wildcards so it should find any file with the 'xlsx' or 'xlsm' extensions.

I haven't tested it so let me know how it goes.

Cheerio,
vcoolio.

[Olly]

Did you try the Power Query solution in post #4, Paula?

[paula.mccall]

good afternoon Vcoolio,

thank you so much for all your help (and patience)!!! I'm traveling this week, but as soon as I return to my office I will give these a try and let you know how it works out. Hope you have a great day!

Kindest Regards,
Paula

[paula.mccall]

Good afternoon vcoolio,

I apologize for the delayed response - been work traveling. I tried using the updated code that you provided for the macro enabled files, but it was quirky (I'm thinking it may have something to do with the way the original file is formatted - maybe?). Today I also made my first attempt at using the power query method, using the code provided by Greg M (thank you again for your help). The power query method works pretty well for the .xls files, but it did not work for the .xlsm files. Also, when i used the power query method, it keeps giving me two extra rows between each set of data - not sure why??
So, because I really need to make some progress on this project (along with my extensive learning curve), I created a new file for the previous macro enabled worksheets and utilized your original code to them - and now it will not work properly (I'm not sure what I am doing wrong). Now, when i run the macros, it gives me all of the data from each sheet each worksheet. Hoping to better clarify my problem, i made an attempt to attach the two different file types that I am trying to copy/paste the 2nd row of data from to the Master. I really appreciate all your time and patience.

Kindest Regards,
Paula

[vcoolio]

Hello Paula,

Which code did you use (post #14 or post#15)?

What do you mean by "quirky"?

Cheerio,
vcoolio.