Automatically updating a workbook with information from several workbooks

[MobiTec]

Hi everyone,

Hope someone can help..

Basicly i will explane the bast i can to get you to understand my problem/task

I have a folder which contains 10+ Workbooks and 1 Master Workbook, Each of the 10+ workbooks are updated daily and contain a sheet named "figures" and at the moment i pull all the data out day by day manually and paste it into the "Master" workbook

Is there a posibility that there is an automated way to pull out that data under the "Figures" tab and update the "master" workbook, i need it to also allow additional workbooks to be added...

Could someone please point me in the right direction and explain the best thay can as i am past the basics of excel but far from advance..

Thank you.

[pike]

Hi MobiTec
Welcome to the forum

this is a good place to start
https://sites.google.com/a/madrocket.../wbs-to-sheets

[JBeaucaire]

Thanks, Pike. Based on the Op's description, I would most likely start with this macro:

Workbooks to 1 Sheet

The parts of the code that need to be edited are colored to draw your attention. Something like this to only take the data from a specific sheet:

Please Login or Register  to view this content.

You would most likely just remove the code to "move the imported files" since you want to keep the files in the same place.
==============

[MobiTec]

Thank you to both of you, that has been a great help..

I do however have a couple of questions...

1, Do the name of the workbooks matter?
I.E. My master workbook is called "October.xlsm" and the other workbooks are called "Week 1.xls", "Week 2.xls" and so on..

2, Do i run this macro in a blank workbook called "October.xlsm"?

3, Do i need all the other workbooks open when i run this in the Master "October" workbook..??

Again thank you for your help so far..

[JBeaucaire]

The macro goes into the workbook you want to collect the date into. It doesn't matter what the workbook is called, just make sure there is a sheet called Master in it.

Edit the macro so the fPath string (it's colored on the website) lists the path to where your files are located.

[MobiTec]

Again, thank you for that..

so this is what i have now..

Please Login or Register  to view this content.

When i run this, it locks up and crashes "not responding" ..?

Any ideas?

[JBeaucaire]

1) Make sure the macro is in the workbook to collect the data.
2) The macro goes into a regular code module (Insert > Module)
3) You don't need these lines any longer, either:

Please Login or Register  to view this content.

If problems persist, upload the workbook with your edited/problematic version of the macro already in it and at least one wb you're trying to import from.

Click GO ADVANCED and use the paperclip icon to post up a desensitized copies of your workbooks.

[MobiTec]

Ok so i tried that, and still crashes.

Here is my master workbook and 2 additional workbooks..

really appreciate your help..

[JBeaucaire]

The files you're importing are .xlsx files but you're filter is looking for .xls files...that's one problem.

Please Login or Register  to view this content.

But I changed that and ran the code and had no crashes.

[MobiTec]

Yep now got that working, thank you. (rep given)

it wasn't working because it was .xls and for some reason it wont read .xls workbooks but if i convert it to .xlsx it works fine.. do you know why this is?

Also, how advance can this go? as my next step is to grab specific data from those sheets,

What would i put if i just want to pull out say row 4 and row 8?

i'm guessing i would replace this bit?

Please Login or Register  to view this content.

But what would i put?

Hopefully after this i should be able to have enough of an understanding to do the rest myself, as you have made this very understandable so far... thank you again.

[JBeaucaire]

You can adjust the copy, paste commands as needed. Expand as needed.

This version assumes titles aren't needed and just copies rows 4 and 8:

Please Login or Register  to view this content.