Automatically Update Data using VBA Code

  • Hi,

    Please advise if it is possible that a VBA code automatically update the data if there is change made to the worksheets?

    I am using the below code to add 38 sheets of data in the same workbook, to 1 sheet called "combined" (also in the same work book)

    But if I make changes to data on any of the 38 sheets, the sheet "combined", does not update automatically.

    So I need to know if there is a way that it will automatically update the "combined" sheet?…68&highlight=#post4395568

  • Re: Automatically Update Data using VBA Code

    There are 2 ways to do that:

    1. Instead of pasting the values from each sheet to the Combined sheet, you could add a simple formula for each cell referring to the the source cell, including the worksheet reference. This will be slow to run, and with 38 sheets of 25 columns you'll end up with a lot formulas (Roughly 1000 times the number of total rows - so 1000 rows total mean nearly 1,000,000 individual formulas) that will affect the performance and size of the workbook. Also, it cannot take account of new additions.

    2. You can have 1 event procedure which runs when a change is made anywhere in the workbook. However, it needs to be able to identify which item on the Combined sheet is to be updated. To do this, each item on the individual sheets must have some identifier. This identifier must be unique across all sheets (except the Combined sheet, of course).

    Brief outline. There's not enough information in your post to give specifics.

    Thank you for the link - your other thread is still to be updated.

  • Re: Automatically Update Data using VBA Code

    Hi Cytop, I think I like your option 2 best, please advise what you mean by an identifier?

    I have 38 sheets, exactly the same, but with different data (students information, like name, surname id number, gender etc.), so I used that code above, to combine all info from those 38 sheets to 1 worksheet, but, lets say I made a spelling mistake on a name from one of the 38 sheets, the code does not update the "combined" sheet automatically. I have to go and delete the tab, and run the code again

  • Re: Automatically Update Data using VBA Code

    I would assume 'id number' is unique per student?

    Post a workbook with some sample data of maybe 5 or so rows and 2 or 3 worksheets with anonymised data.

  • Re: Automatically Update Data using VBA Code

    And are the first characters of that ID, up to the Gender indicator, unique to each sheet?

    If so, then the ID can be generated as soon as someone starts adding a new record, or at least after they have entered data into a cell on a new row. It would be a lot easier to generate a sequential number then.

  • Re: Automatically Update Data using VBA Code

    The last 4 digits will be unique if it can be incremented, 1001,1002 for ward 1 and 2001,2002 for ward 2, and so on

    How should I generate a sequential number?

  • Re: Automatically Update Data using VBA Code

    Snag with your numbering scheme...

    You said the number starts with 1 for Ward 1, 2 for Ward 2 etc... These are padded out to 4 digits for the sequential number part of the ID. No problem, except when it gets to Ward 10, that will presumably start '10', but when padded to 4 digits it will possibly duplicate Ward 1 numbers (...1001) if the gender is the same. Regardless, you are guaranteed duplicates with that scheme.

    The workaround is that the ward number are padded to 2 digits, likewise the sequential numbers. Ward 1 numbers will start ...0101, ...0102 etc but Ward 10 numbers start ...1001, ...1002.

    This is the only way to guarantee unique IDs.

  • Re: Automatically Update Data using VBA Code

    Attached copy Updates the combined sheet when changes made on any of the Ward sheets. If you add a new item to the ward sheet, a sequential ID (numbered as above) will be generated as soon as the Gender is entered. THe Gender is part of the ID so cannot do that until then.

    Once gender is entered and it has an ID, that row will be added to the Combined sheet.

    This does not handle cases where a student is deleted. The combined table will have to be rebuild for that.

  • Re: Automatically Update Data using VBA Code

    Thanks, but this is now getting to complicated for my brain, to even try and understand exactly what you did

  • Re: Automatically Update Data using VBA Code

    Unavoidable, I'm afraid. That works as you wanted - It may be possible to write it a little more compactly or simply, but by the nature of things the analysis phase was very short so everything was written on the fly.

    Regardless, even if someone spend some time analysing the logic and flow the end result would be very much the same, maybe slightly less code lines overall (invariably, more compact code is even more difficult to understand); but the logic would probably be identical.

Participate now!

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