Master spreadsheet populated from worksheets in same file

[ExpressAdam]

Hello, firstly apologies for yet another query of this sort. I have spent quite a long time today reading all the various threads relating to this and the replies and I have tried to use the codes from these threads to solve my problem but it just never works for me. it either messes up teh formatting or just doesnt display correctly.

I have a master worksheet with 6 other identical worksheets in the same workbook. This workbook is on a shared drive so that 6 other people can add in information simutaneously using drop downs and text.

What I need is for that master worksheet to be populated automatically as and when people update thier own tabs (1-6).

I have attached the said workbook and prey someone here can help me

Thanks in advance for any help

Adam

[TMS]

If it's a shared workbook, I wouldn't really want to have anything to do with it ... disaster waiting to happen.

That said, the only code you have is a Worksheet Activate event on the Master sheet. So, the only time that will fire is if you select another worksheet and then re-select the Master. And, even then, it probably won't do what you're expecting as I think it's just going to copy the last row.

You would need to change it to be a Workbook_SheetChange event handler in the Workbook Class module. The code would probably be appropriate but you'd refer to the "sh" variable which is passed to the event handler. I would recommend that you don't use Sheet or sheet as a variable as it could easily cause confusion as it is probably a reserved word in Excel/VBA (albeit, it doesn't seem to be causing a problem)

Regards, TMS

[ExpressAdam]

Thanks for the reply but I just saved over the workbook with the code attached so dont have it anymore...im back to a blank version now with slight modification (deleted some rougue data)

That said, I'm not that up to speed on Excel clearly as I understood only parts of what you was saying.

Apologies.

I have added my current work book.

[ExpressAdam]

Maybe it would be better for me to have a master document and have 6 other documents feeding into it rather than it all in the same workbook. This would surely make it safer for network use

[TMS]

This would surely make it safer for network use

Yes, and no. If you want it to update automatically in real time, you would need each of the Slave workbooks to have a Worksheet Change event handler that updated the Master whenever a record has been completed. However, the potential issue with that approach is that the code will need to cater for contention. That is, how do you cope with two users updating their workbook at exactly the same time? The likelihood is low, but it could happen.

However, if you are content to collate all the data at the end of the working day, or perhaps overnight, you could schedule a task to merge the individuals' personal workbooks into the Master. You could use Windows Scheduler to automatically load the Master workbook, say, at midnight. The Master workbook could have a Workbook Open event handler that launches a macro to merge the day's transactions.

The latter would be my preferred option. As you say, safer for network use. No privacy issues. No opportunities for one person to enter data in the wrong worksheet. No complex sheet management based on login IDs. No contention issues. No implicit or explicit shared workbook issues. Big win in my opinion.

Regards, TMS