Posts by christianhau

    Re: Automatic Updating Of Master Worbook From Slave Workbooks

    Hi again! I was hoping you would grant me one last favour. Can you comment your code? I am trying to customize it a bit since there is atleast one more date field that has to be compared in the same manner as what the code is doing right now. But I am struggling with understanding your code so if you could explain it I would be veeeery grateful. If you don't have the time I can always ask a direct question as well, let me know!

    Re: Automatic Updating Of Master Worbook From Slave Workbooks

    Hi again! Sorry to be bothering you..

    As I said the code works flawlessly but I was wondering if you could help me with adressing some performance issues? Right now my two slave workbooks contain about 3500 rows each that is checked with the code. This takes approximately 10 minutes to do and I was hoping to get the performance up a bit. Do you have any suggestions to where in the code the program might hang or perform poorly?


    Re: Automatic Updating Of Master Worbook From Slave Workbooks

    Sorry for the late reply, a weekend without internet connection :)

    The problem is that all the other columns will have data in them, they might be empty but they might also have data in them. So the point is to have this working without any interference with the other columns, is that possible? I only say this since you stated in you last post that column A:D could not contain any data, and in my original worbook every column from A to AF will contain data.

    I am really making this hard for you aren't I, please ask me if anything is not clear from my side, I will be more than happy to explain!

    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"

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

    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.

    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

    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

    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

    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

    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

    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

    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:

    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

    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?

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

    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

    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.

    '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!!


    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!