+ Reply to Thread
Results 1 to 15 of 15

Consolidating data from Multiple Workbooks to One Workbook issue

  1. #1
    Registered User
    Join Date
    09-16-2012
    Location
    NZ
    MS-Off Ver
    Excel 2007
    Posts
    11

    Consolidating data from Multiple Workbooks to One Workbook issue

    I am a "medium" Beginner when it comes to VBA and have a project I am working on, however I keep getting 1004 errors so hoping for another way to do this.

    Each of my 7 sales people have a workbook, in which they save each sale they do. One worksheet in each workbook has a colum with total sales points for the week.

    What I am trying to do is have my Master workbook pull the weekly points totals for each person so throughout the week I can see how my salespeople are tracking.

    Now I have tried simply clicking in my master worksheet and putting = then clicking on the cell in the salespersons weekly summary cell, creating a link. I have also used index/match.

    My problem is when I click data/edit links.udate values some of the salespeoples workbooks wont update without opening the file. This in turn throws a 1004 error with a macro I write to update the data from the salespoeples sheet to my master sheet.

    Does anyone have a better solution for me? I am happy to start from scratch with my master sheet to make this work.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,572

    Re: 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
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    09-16-2012
    Location
    NZ
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Consolidating data from Multiple Workbooks to One Workbook issue

    Thanks,

    My sales people click save each time they update thier workbook. What I need to be able to do is click update throughout the day to see how we are tracking for the week without having to close and re-open.

    I also get the same error when I open my master workbook.

    Ocassionaly it will update with no errors, but more often then not there is an error.

    All on excel 2007 i should mention

  4. #4
    Registered User
    Join Date
    10-18-2012
    Location
    Planet Earth
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Consolidating data from Multiple Workbooks to One Workbook issue

    Can you post the code with an example of said error?

  5. #5
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,066

    Re: Consolidating data from Multiple Workbooks to One Workbook issue

    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
    HTH
    Regards, Jeff

  6. #6
    Registered User
    Join Date
    09-16-2012
    Location
    NZ
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Consolidating data from Multiple Workbooks to One Workbook issue

    Quote Originally Posted by Shmemtar View Post
    Can you post the code with an example of said error?
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    10-18-2012
    Location
    Planet Earth
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Consolidating data from Multiple Workbooks to One Workbook issue

    @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?

  8. #8
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,066

    Re: Consolidating data from Multiple Workbooks to One Workbook issue

    Quote Originally Posted by Shmemtar View Post
    @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.

  9. #9
    Registered User
    Join Date
    10-18-2012
    Location
    Planet Earth
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Consolidating data from Multiple Workbooks to One Workbook issue

    Quote Originally Posted by jeffreybrown View Post
    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?
    Last edited by jeffreybrown; 10-19-2012 at 07:12 AM.

  10. #10
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,066

    Re: Consolidating data from Multiple Workbooks to One Workbook issue

    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.

  11. #11
    Registered User
    Join Date
    10-18-2012
    Location
    Planet Earth
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Consolidating data from Multiple Workbooks to One Workbook issue

    Jeff, this code seems to overwrite the source sheets in the varSheets array on top of each other in the destination workbook. In other words, I have it running for 2 sheets in the varSheets variable ("Test1", "Test2"), and it will display the data from Test1 (starting in cell A2), and then overwrite the data from Test2 on top of the other data, however starting in cell B2. Is there a way to append the data in Test2 underneath the data from Test1?

  12. #12
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,066

    Re: Consolidating data from Multiple Workbooks to One Workbook issue

    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.

  13. #13
    Registered User
    Join Date
    10-18-2012
    Location
    Planet Earth
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Consolidating data from Multiple Workbooks to One Workbook issue

    Will do, sorry Jeff, sorry jeffvnz!

  14. #14
    Registered User
    Join Date
    09-16-2012
    Location
    NZ
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Consolidating data from Multiple Workbooks to One Workbook issue

    Is there an easier way to make this work?

    Last night i descovered when I go to Data, select all and click update values when all of the source files are closed, it updates the data without issue. 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.

    Any ideas?

    Picture1.jpg

  15. #15
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,066

    Re: Consolidating data from Multiple Workbooks to One Workbook issue

    Quote Originally Posted by jeffvnz View Post
    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.

    Quote Originally Posted by jeffvnz View Post
    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?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1