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

 

[palaeontology]

Ok, I am now using the following code to create dynamic named ranges ... one range for each Year 10 Maths class ... my attempt at the code did the job but was clumsy and long, being about 60 lines long, so thankyou to Akuini for streamlining my code to this beautifully succinct piece ....

Sub DynamicNamedRanges()
Dim i As Long, j As Long
Dim va
va = Range("C1", Cells(Rows.Count, "C").End(xlUp))
For i = 7 To UBound(va, 1)
    j = i
    Do
        i = i + 1
        If i > UBound(va, 1) Then Exit Do
    Loop While va(i, 1) = va(i - 1, 1)
    i = i - 1
    ActiveWorkbook.Names.Add Name:="Dynamic_" & va(i, 1), RefersTo:=Range(Cells(j, "A"), Cells(i, "AH"))
Next
End Sub

I went with naming the ranges by Class Code rather than by Teacher, as a teacher can have more than one Class.

So, the Master file (called 10MAA) will have as many copies made of it as there are classes within that subject, and each copy will be named for that particular class.

For example, there might be 7 classes studying 10MAA ... the class names might be ... 10MAA501, 10MAA502, 10MAA503, 10MAA504, 10MAA701, 10MAA702, 10MAA703.

So, in this case, 7 copies would be made of the 10MAA Master file, and each would be named using the 5-letter/digit code you see above ... eg: 10MAA501

Each of the copies would have my dynamic named range code embedded in the 'Entry' ws, so each class group would have a dynamic named range ... as seen in the image below ...

Now that the Class ranges have been dynamically named, I need to look at how to code for the transfer of data within a named range over to the Master file.

I need some code that would be able to be added to a Command Button, that when pressed would do 2 things ... in this order ....

1. transfer all data from the named range that bears the same name as the copied File the teacher is working in, over to the Master File called 10MAA. (so if the teacher of 10MAA503 pressed the command button, all the data from Entry!$A$60:$AH$85 from the file called 10MAA503 would be transferred over to exactly the same range in the 'Entry' ws in the Master file called 10MAA)

2. transfer a copy of the entire 'Entry' ws from the Master file back to the 'Entry' ws the teacher is currently in (I need this, because other teachers will likely have been entering data at the same time (into their own class file copy) and all teachers need to be able to see the most up-to-date version of the Master file without all having to access that Master file at the same time)

The location of the Master file named 10MAA and each of the copied files would be stored at .... G:\Maths Dept\Student Results\2019_10MAA

Is that 2nd step (for the command button) possible while the teacher is still in the file, or would they have to exit first then return to see the updated 'Entry' ws ???

My vba is poor, at best. I'll try a few things my end, but suspect I'll get muddled on the fact the named ranges are dynamic and not static. Would anyone like to attempt to piece together some code that would do this transfer ?

Very kind regards,

Chris

 

[Roger Govier]

Hi
Sorry, I have been off line for a while. My approach to the problem as now described would be:-

Assuming each row in the master will have some unique identifier to the Class the student belongs to.

After the Teacher had modified his students then I would save that Copy file.
Filter the copy data to show only members of the class.
Having done that, open the "Master" file, having first checked that it is not already open with another user.
Filter the data to show only the rows belonging to that class.
Delete the filtered selection.
Determine where the last row row now exists in Master
Copy the Filtered data from the copy file and paste to Last Row +1 in the Master file
Sort the Master file by whichever field is relevant to get the data into the same order as it originally was.
Save the Master file
Close the Master file

This is a technique I use often for client. There is no need to be concerned about which rows have been amended, nor whether new students have been added to the class. You are deleting all existing entries for that class, and then pasting in everything about that class as last modified by the teacher.
Absolutely no need to keep pointers or datestamps or anything else.

Clearly you need to include checks to ensure that the Master file is not already open with another user and if so initiate a wait for a short time and retry. The Master file would not be open for very long each time the code runs, so in practice there would probably be very few wait states.

 

[palaeontology]

Hi Roger, thankyou for your thoughts, however I am trying to create a system that does not require the user to do anything other than enter the data and push a button before exiting.

The reason is that the vast majority of teachers who will be using this have almost no knowledge of excel other than opening and closing the file.

The system has to be a stand-alone, with no requirement of the user whatsoever. Also, the whole point of what I'm trying to create is to alleviate the need for any teacher to have to wait to access one spreadsheet (for example, the Master) while someone else is in it.

I think I'm close. I think I'm on the right track with what I wrote in my last post, so am now just trying to put together the code for the command button to do those 2 things I listed, though I'm looking into whether the 2nd item could be managed with a 'linked' table .... still playing around with that one.

Thankyou, again, for looking into my task.

Very kind regards,

Chris

 

[Roger Govier]

Hi Chris
I was setting out the steps for you to write the code, which would be attached to a button on the sheet that the teacher could press..
That method is the simplest and fastest in my experience.

I was not suggesting that you gave those instructions to the teacher to perform, as inevitably they would get it wrong!!!

 

[palaeontology]

oh, my apologies, I misunderstood.

I'm playing with recording what you suggested, but as mentioned earlier, am poor, at best, at writing vba, so when it comes time to write the vba to find where the last row in the Master file is, I wouldn't know how to do that.

I think much of the rest I can adapt from the recording.