Consolidating data from Multiple Workbooks to One Workbook issue

if you can make sure that all of the files have the same format (rows/columns/cells in the same place), then a simple =cell will work for you. doing it this way, you will not need to update links, they should update as soon as you open the file

Since you mentioned the possibility of starting over, I would look into some of Ron's pre-bulit code...

Start with Ron's main page and then the links below are to specific sections that may be right up your alley. Make sure you read Ron's notes carefully as he takes great steps to show you how to modify the code to some of your specific needs...

Ron's Excel Tips

http://www.rondebruin.nl/copy3.htm
http://www.rondebruin.nl/ado.htm
http://www.rondebruin.nl/fso.htm
http://msdn.microsoft.com/en-us/library/cc837974.aspx

Originally Posted by Shmemtar

@jeffreybrown, it looks like Ron's solutions are more ideal for a one-off situation, correct? In other words, they wouldn't allow for a dynamic master table that can be updated frequently and easily?

I couldn't begin to answer your question as I really don't know your requirement or layout.

One of the project at work I use Ron's "get data" macro and it is incredible easy and ideal for frequent updates. The key is understanding what you want to achieve.

Originally Posted by jeffreybrown

I couldn't begin to answer your question as I really don't know your requirement or layout.

One of the project at work I use Ron's "get data" macro and it is incredible easy and ideal for frequent updates. The key is understanding what you want to achieve.

I apologize for the vague question. Upon further review of Ron's code in http://msdn.microsoft.com/en-us/library/cc837974.aspx, my question are as follows.

1. Is it possible to perform the "get data" macro, however instead of have it create a new workbook every time it is executed, have it simply update one which is currently open? i.e. Would there be something to replace here:

Please Login or Register  to view this content.

2. More like a 2nd part to the first question (if it is possible), would there be a way to have the macro populate an existing table in an existing worksheet with headers, and therefore every time the source files in the array are updated and saved, the Combined Sheets, or MasterSheet, could update with the new information by simply running the macro?

Here is what I would do using Ron's GetData macro.

This macro will work through the list of sheets in the varSheets variable.

Please Login or Register  to view this content.

There is a way, but I would ask that you follow the forum rules and create your own thread. You can reference back to this thread if you want, but this thread belongs to jeffvnz and we need not hijack it.

Originally Posted by jeffvnz

Is there an easier way to make this work?

To make what work? Are you using links to update the values? I don't know your project.

Originally Posted by jeffvnz

However during the day when the source files are open I get the error below, which I believe is causing the 1004 errors I get with my simple macro.

I don't know what your simple macro is? Can you elaborate and fill in some holes?