Link changes in one spreadsheet with identical original

[palaeontology]

From the start, let me say that what I want to achieve isn't possible with Google sheets due to the number of macros and vb written into my spreadsheet.

I'd like to replicate the functionality of a spreadsheet that a former Department Head made (several years ago) that allowed multiple users to add changes to the one original spreadsheet without them all being in that one original spreadsheet at the same time which is a big issue for us ... maths teachers trying to access the same spreadsheet at the same time.

He made it so that the original 'Master' spreadsheet had several exact copies made (one for each teacher who teaches that particular subject).

Each copy was coded, so when the teacher had finished adding their changes (which would only be for the students in their particular class .. hence, no other teacher would ever be changing the same cell on their particular copy of the original as each student would have their own row) they would close the spreadsheet, and on closing, the code would do two things ....

1. save any changes (that had been made to that copy since it's previous save) over to the original.
2. save the copy as a copy of the newly updated original

The Department Head left a few years ago and never shared with us how he coded the spreadsheets.

As mentioned earlier, Google Sheets won't do this for me as my spreadsheet has far too many macros, UserForms and VB coding.

Can anyone think what the coding might have been that our former Head used ?

Any help on this would be greatly appreciated.

Very kind regards,

Chris

 

[Roger Govier]

Hi
Assuming you had a Master file called overall, with each sheet being the name of the individual teacher.
And assuming that each teacher had a file which ws their name, with a sheet where the grades were recorded which was also named with the teacher's name e.g. Mr Smith

Then the following code pasted into a module in each of the teacher workbooks, would probably achieve what you want.

Sub CopytoOverall()
    Dim shname As String
    shname = ActiveSheet.Name     ' e.g. Mr Smith
    ActiveWorkbook.Save
    ActiveSheet.Range("A1").CurrentRegion.Copy
 
    Workbooks.Open Filename:="R:\Overall.xlsb"  ' change to suit file location
    Sheets(shname).Select
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.Save
End Sub

Adapt as necessary and change A1 if the data does not begin in cell A1

 

[Fluff]

You'll probably find the code in the "ThisWorkbook" code module.

 

[palaeontology]

I realise now I didn't explain myself very well.

The Master file which you've called 'Overall' didn't contain a worksheet dedicated to each teacher. It did have about 25 sheets, all doing different things necessary for the various UserForms involved with the student profiles, etc, but all the sheets related back to the worksheet called 'Entry'.

The Master File, might have been called ... 10MAA .... and the 'Entry' worksheet within that file contained the list of all students studying Yr 10 Maths Advanced (coded 10MAA).

From the image above, you can see the Master File listed ALL students and their classes etc (the image only shows about the first 45 students out of the 300 or so who studied the subject).

Each class (within 10MAA) had their own copy of that Master File (with the 25 or so sheets) and were called, for example .... 10MAA(301) or 10MAA(302) or 10MAA(711) etc etc.

A teacher would access their relevant class copy of the Master file, go to the 'Entry' worksheet, scroll down the list until they came to their corresponding class group, enter the results of their students for a certain assessment piece, then they'd exit.

Upon exiting, the code would do those 2 things I mentioned ...

1. save any changes (that had been made to that copy since it's previous save) over to the original Master File.
2. save the copy as a copy of the newly updated original

So, if the teacher of 10MAA(704) entered data into the 10MAA(704) file [at let's say 10:20am], and exited(saved) and the teacher of 10MAA(413) entered data into the 10MAA(413) file [at let's say 10:25 am], and exited(saved), then both the 10MAA(704) and 10MAA(413) data would both appear in the relevant rows and columns of the Master file called 10MAA.

I imagine this new explanation would change the code you provided considerably.

 

[palaeontology]

Hi Fluff ... we no longer have the file our former Head of Department made ... he got the sack for something else, and in his wrath, took everything related to any spreadsheet he ever made. He covered his tracks very well ... our IT department could find no trace of the spreadsheets.

The spreadsheets I use now (with macros, vb and UserForms) are of my own creation, but the other teachers complain all the time about having to wait for another teacher to exit the spreadsheet before they can enter their new data ... sometimes a teacher will forget to exit the spreadsheet for 24 hours or longer, meaning no one can access it in that time, so this is an attempt to make my spreadsheets as simultaneously accessible as they used to be under our former Head.

Chris

 

[Fluff]

our IT department could find no trace of the spreadsheets.

No secure backups, on a server only IT can access

The first thing you have to do, is decide how to tell what needs to be copied & what doesn't.

 

[palaeontology]

You're right ... would he have coded to create the 'History' worksheet that lists all changes (ever made since the sheet was first created) ... if so, maybe he used the information found in columns G (the cell changed) and H (the new value entered in that corresponding cell).

However, he would have needed the code to recognise only those changes since the previous save ... he could have done this by looking at the time and datestamps in columns B and C.

This is way beyond my coding skills though.

Any thoughts, or do you think I'm on the wrong track ?

Kind regards,

Chris

 

[Fluff]

As with all things Xl/VBA, there are any number of ways of doing it.
If each teacher would only change a particular section, I'd probably look to just copy that entire section.

 

[palaeontology]

Again, you're right.

I was originally aiming to first identify the date and time of last change, then determine what changes had occurred since that last change and in what cells. I was then going to try to code to transfer only those changes to the corresponding cells in the Master file, however, I've just realised that a user will quite likely save multiple times as they enter new data before wanting to leave the spreadsheet, so not all the changes would be transferred ..... so I think your idea of just copying the section relevant to a particular teacher is the way to go.

A problem I foresee is that as the year progresses, new students can be added to a particular teacher's class, so the range of rows relevant to a particular class can change from time to time.

Perhaps if we code to identify which rows have the particular teacher's 5-letter code in column D (in the 'Entry' ws), and transfer only those rows over to the Master file (in this example, called ... 10MAA).

 

[Fluff]

That sounds as good a way of doing it, as any.