Combining data dynamically

**AMK4** · 01-10-2007, 11:41 PM

I have a workbook that contains several sheets. Each sheet represents a customer and contains several pieces of information. The sheets are named with a combination of the customer's name. Then there's also one master sheet where I'd like to combine some data dynamically.

Each customer sheet has a section that's used like a ledger, keeping track of when someone paid, how much, and what their balance due is. This data gets updated several times a month.

Now, on the master sheet there's a single, larger ledger where all of the individual ledgers get aggregated. However that's a manual process. Someone updates a payment on the customer sheet, then they switch over to the master sheet and duplicate the data there. Next customer, update their sheet, then come back and update the master ledger.

Is there some way to dynamically update the master ledger sheet, AS THE INFORMATION IS ENTERED. In other words, as soon as one is done entering a line item on the customer's sheet, it automatically shows up on the master sheet. We don't want the data sorted on the master sheet, just displayed as they're entered.

So, if I update three of those customers (on three separate sheets) with 5 payments in this order:

Please Login or Register  to view this content.

...that's also the order in which I want them to show up on the main ledger, and NOT grouped together in any way.

If I stop in the middle of entering data, say after the 3rd customer, I want to be able to switch to the master sheet and see those three customers I just entered.

So, is this doable in VBA? Also, what happens if I go back and change one of those lines on any of the customer's sheets? How can I have the master ledger also updated?

Let the creative juices flow...

**MSP77079** · 01-10-2007, 11:56 PM

This is very doable using VBA. I would suggest using a workbook event procedure ...

Please Login or Register  to view this content.

This procedure will fire each time any change is made to any of the worksheets. The first line of code would tell it to stop immediately if the change was made to the Master sheet (to avoid double entries and a continuous loop!).

Please Login or Register  to view this content.

To do more, I would need to know which column always has data in it. If it is column A, then you would use something like this to determine the next available row on the Master sheet:

Please Login or Register  to view this content.

Then, copy the data ...

But, ya know, the problem I just realized is that this is going to copy the same row multiple times as you make an entry in each cell on the customer sheet. So, I guess I also have to know, what is the LAST entry that will be made each time? Then, we only run the copy routine when the entry in the customer sheet is finalized.

Can you upload a skeleton of your workbook? Remove anything you don't want the world to see. Save it as a zip file, then upload it.

**AMK4** · 01-11-2007, 02:33 AM

I'm assuming you want it with the ledgers already in place. This is fine, I can strip the sheets. The only question though, what happens if some time in the future something changes, a column gets added or removed (before the ledger section), or if I have to re-arrange the sheets all together?

**MSP77079** · 01-11-2007, 08:55 AM

what happens if some time in the future something changes, a column gets added or removed (before the ledger section), or if I have to re-arrange the sheets all together?

You need to be sure that you understand the VBA macro(s) so that you are able to maintain the code if anything changes.

**AMK4** · 01-11-2007, 02:38 PM

Originally Posted by MSP77079

You need to be sure that you understand the VBA macro(s) so that you are able to maintain the code if anything changes.

Okay, that won't be a problem. So back to my previous question, do you want the workbook with the ledgers in place, where they currently are placed (correct rows/columns and all) or does the placement not matter?