Automatic Updating Of Master Worbook From Slave Workbooks

  • Hi!


    I have basically read all the posts on similar subjects and have tried for a few days to make it work but my solution is far from good enough. So now I am asking for you help.


    My project: I have a Master workbook that contains the complete sales for the entire business. This is based on input from three different departments. These three departments have their own Excel workbook that they enter information into. The four files are all located in the same directory on a network folder.


    What I want to do is to automatically gather all the entries from the three slave workbooks to the master workbook whenever the macro I am trying to create is run. There is no way of knowing how many new entries each workbook will contain and they have to be added so that that they don't overwrite eachother and so that they are put in sequence after the last row in the master workbook.


    Now the information to be gathered is located on the first worksheet in all the three workbooks. The destination sheet in the Master workbook is also the first worksheet in the workbook. The structure of the information that I want to copy is equal on all the worksheets meaning that one sale is entered as a new row in the sheet. It is this row that I want to copy to the mastersheet.


    What makes it a bit more complicated is that I want to extract certain information from one sheet and different information from other sheets. From one sheet i want to copy the entire row and from another sheet I just want to copy certain figures like Order Number, Customer, Price and so on. Meaning that I will have a different set of what I want to copy depending on what workbook I am copying from.


    Here is what I have so far, it is not working by far and I tried to aim for something simple to start with since my vba experience is limited. So far I cannot copy anything into my Destination master file.



    So if anyone can help me I would be very grateful!

  • Re: Automatic Updating Of Master Worbook From Slave Workbooks


    Not sure it's quite what you want... Try :

  • Re: Automatic Updating Of Master Worbook From Slave Workbooks


    Thank you so much for taking the time :)


    I like your solution regarding the different cases, looks a lot like what I am looking for!
    I do although have some questions since I can't make it copy some sample data for me.


    I want the destination file to be the same as the file we opened. There is only one file that should be written to and that is the destination file. None of the files change filename.


    I was just wondering about this because of this code snippet, does this have anything to do with the pasting of data? As far as I could tell from the code you are pasting to Destination since you specified it to be ThisWorkbook.

    Code
    'Set Paste target first
              myFileName = Dir(.FoundFiles(i))



    Is there any reason why there is nothing being copied from my three sample files to my sample master file? My sample master file is empy and my three slave files are just filled with some random numbers to see if i could do the copying. And yes, I did change the file location :) Thanks again!!

  • Re: Automatic Updating Of Master Worbook From Slave Workbooks


    First, sorry for the folder change ;-))
    As your code works, you copy cell A1 of sheet 1 from the first file, cell A1 of sheet 2 from the second file, etc.
    I think you should replace :

    Code
    Source.Sheets(i).Range("A1").Copy Tgt


    with

    Code
    Source.Sheets(1).Range("A1").Copy Tgt


    I tested the modified macro and it seems to work (provided you only want cell A1 to be copied).
    As regards to :

    Code
    myFileName = Dir(.FoundFiles(i))


    it only returns the file name, which I use in the Select Case - End Select part.

  • Re: Automatic Updating Of Master Worbook From Slave Workbooks


    Thanks again! Changed it so that it copies from sheet1 every time. But it is still not working.. Hmm, as far as I can tell it should be working. Just to make sure that I understand it all this code snippet takes whatever is in A1 of sheet1 in the different workbooks and copies it to the Tgt value?

    Code
    'Copy and paste to destination
              Source.Sheets(1).Range("A1").Copy Tgt


    And the target value is defined to be the first row after the last inserted row in the Destination workbook?

    Code
    Set Tgt = .Cells(.Rows.Count, 1).End(xlUp).Offset(1)


    If I understand this correctly then why is nothing being copied from my sourcefiles to my destination file? Have you tested it in a folder with four files, three input files and one master file? And just some random data in the input files?
    Thank you so much again!

  • Re: Automatic Updating Of Master Worbook From Slave Workbooks


    Ahhh... thank you! Finally realized what I was doing wrong!!! Didn't realize that the .FoundFiles also counted the workbook that the macro was written in. So that value ended up being four and so in the last iteration of the loop it copied a blank value from itself and pasted that where I was expecting to see some values. So by changing the for loop to:

    Code
    For i = 1 To 3 '  Loop through all


    it never uses the Destination sheet as an input sheet. Sweet! Thanks for the help guys!

  • Re: Automatic Updating Of Master Worbook From Slave Workbooks


    Short follow up question: What do you think would be the best solution to make sure that only the new additions to the source workbooks are copied into the destination workbook? Whenever you run the macro i want to cope only the newly added entries. I figured that I need to store a variable indicating the last copied row but where do I store it? In the sheet itself?


    Any other suggestions are also very much appreciated!

  • Re: Automatic Updating Of Master Worbook From Slave Workbooks


    If the department ID is stored in the sales row, you don't need to store anything, it will be simple to retrieve it :
    1. retrieve the last row in the master file;
    2. retrieve the corresponding sale in the source file.
    If it's not the case, yes, store it in a cell of the master file.

  • Re: Automatic Updating Of Master Worbook From Slave Workbooks


    Its me again :) I am getting closer to getting my script running smoothly but ran into a bump.


    My master workbook is now updated with all the new rows that have been added to the slave workbooks since the last time it was opened. Now the problem is that there is one cell in each row that is not always filled out. This is a total sales sheet and the orders are filled in as they arrive but the actualy payment date will not show up untill the merchandise has been shipped to the customer.


    This means that I want the row to be copied to the master sheet from the slave even if the payment date is not set. But I then want to make sure that if the payment date is set it is updated to the masterworkbook.


    Does this mean that I have to traverse the entire worksheet (several thousand entries), every time I open the master workbook, and crosscheck every blank payment date with the actual entry in the slave workbook to see if the payment date has been added since the last time? Or is there an easier way?


    If there is then please do enlighten me :)


    Thanks again!

  • Re: Automatic Updating Of Master Worbook From Slave Workbooks


    Quote


    Does this mean that I have to traverse the entire worksheet (several thousand entries), every time I open the master workbook, and crosscheck every blank payment date with the actual entry in the slave workbook to see if the payment date has been added since the last time? Or is there an easier way?


    Yes, but you did not have to read each row. You should filter the data to find the empty payment dates and work on the filtered rows. Alternatively, you may use the "Find" instruction to find blank payment date cells. Feel free to post a sample file, and i'll return the corresponding code.

  • Re: Automatic Updating Of Master Worbook From Slave Workbooks


    Great! Here is a file with two columns representing a selection of the data that I am working on. The ID column is a unique indentifier for the row. Instead of attaching two workbooks I made two sheets instead since I presume that the method you use would work equally good on two sheets as it would on two workbooks. But in my case it is two different workbooks both with the information on the first worksheet. If you need anything else please let me know, thanks in advance!

  • Re: Automatic Updating Of Master Worbook From Slave Workbooks


    I moved the "updated slave workbook" sheet to a new workbook which i named "SlaveBook.xls". Paste the following code in the "ThisWorkbook" module :


    As you have 3 slave files, you'll have to filter the department column too.

  • Re: Automatic Updating Of Master Worbook From Slave Workbooks


    Thanks! This code is too advanced for me but working on understanding it now. Is there a way to not just display the rows that were changed after the macro is done? I want to update the master workbook but leave how it is displayed intact.
    Thank you so much again!

  • Re: Automatic Updating Of Master Worbook From Slave Workbooks


    Try :


    A missing dot that matters !

  • Re: Automatic Updating Of Master Worbook From Slave Workbooks


    didnt realize the genius of VLookup before now :)


    But struggling a bit here, first time I ran the macro it went perfect! but when i removed some random values from the Payment Date field in the Examplefile.xls and ran it again I just end up with values like 39126. And I cannot figure out why..


    Another thing. In my actual sheet the Payment Date is located in the AA column and the ID is located in the E column. In between there is plenty of data that should not be messed with is the following then a working solution?


    And another thing. Since I have to check in possibly three different sheets for the corresponding ID and Payment Date, how do I do this?

  • Re: Automatic Updating Of Master Worbook From Slave Workbooks


    1. 39126 means "13/02/07"; It's the way Excel computes time (day 1 is "01/01/1900" (sometimes "01/01/1904). Enter that date in a celland apply standard number format and you'll get 39126. I added a line of code to correct it :

    Code
    c.Offset(, 26).NumberFormat = "dd/mm/yy"


    The macro I post below should certainly be modified. I used column C to add the department. Moreover, if column A is not used, the filters instructions have to be modified. I created three department files :
    - dept1.xls
    - dept2.xls
    - dept3.xls
    I am just thinking that I did not change their structures (IDs in column A and dates in B).
    You better send a new sample file. Anyway, here is my code (see also attached file).


    Of course, I'll add comments when the macro will be finalized.

  • Re: Automatic Updating Of Master Worbook From Slave Workbooks


    Again.. Thank you so much for the help!! You got skills man :) But since I am struggling a bit with changing your code to my suit i do humbly ask for some further assistance.


    So! To make it easier for you and to exclude any guesswork, here is a to the point examplefile of how my files look. I wasn't sure if you wanted me to put data in the other columns but since there is a chance they could be blank anyway I left them untouched. The columns that are important are now as before the ID and the Payment date. But I have now put them where they are in the original document. I also included a a type column that signifies if the row came from Slavebook or Slavebook2.


    Slavebook is signified by an M and Slavebook2 is signified with an I. I hope this makes it a bit easier!


    I decided to leave out workbook number three since it might turn out to be excluded anyhow, so to save worktime here is my examplefiles!

  • Re: Automatic Updating Of Master Worbook From Slave Workbooks


    If I understand you correctly here is the overview of the first column of data:


    Master Workbook:
    ID: Column "E"
    Type: Column "N"
    Payment Date: Column "AA"


    Slavebook:
    ID: Column "E"
    Payment Date: Column "AA"


    Slavebook2:
    ID: Column "A"
    Payment Date: Column "J"


    The data that is lacking is the payment date in the master workbook and it is to be gathered from the payment date in slavebook 1 and 2 at different locations in the workbook depending if it's slavebook 1 or 2. If this does not answer your question then please clearify.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!