VBA code to automate a manual timetable

[Eng mona]

Hi Folks,
I would appreciate any advice on how to automate this spreadsheet as much as possible it can be.
It takes me a lot of time to finish this since there are many moving parts, and it needs to be linked to other tabs.
This is a timetable for an exam that runs in multiple rooms a day.
In the attached template, you can see that there are multiple rooms and each one of these rooms has stations indicated from 1a to 6a and 1 b to 2b.
I need a VBA code that can apply the following rules:
- The list of names in the second tab is to be filled out horizontally in the empty slots in the timetable in the first tab. The first name goes to the 8:10 row in station 1a, and the second name goes to station 2a and until station 6a, and then move to the second row at 8:40 and again fill the names horizontally until the names list in the second tab is finished.
- Cells highlighted in dark grey (MOD) and cells highlighted in red (Break) in the timetable tab shouldn’t have any names assigned – they should stay blank.
- Rows with Examiners initials and not start time shouldn’t be assigned any names.
- Name assigned in station 1a to be assigned again in station 1b and name in station 2a to be in station 2b and the same until 6a and 6b (names must stay with the same station number and move from a to b) (this is an exam where a student go to station 1a for 20 min and then go to 2b the next 20 min)
- Case Number columns should not be assigned any names.
If the spreadsheet is too complicated and has a lot of moving parts that will make the VBA code difficult to run, I a happy to adjust it.

Many thanks in advance for any help.

[HansV]

Does this do what you want? Please test on a copy of the workbook.

Code: Select all

Sub FillTimeTable()
    Dim ws As Worksheet
    Dim wt As Worksheet
    Dim s As Long
    Dim t As Long
    Dim m As Long
    Dim c As Long
    Application.ScreenUpdating = False
    ' Source sheet
    Set ws = Worksheets("Names")
    ' Target sheet
    Set wt = Worksheets("Timetable")
    ' Start target row
    t = 4
    ' Start tatget column
    c = 3
    ' Last row with a name
    m = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ' Loop through the names
    For s = 2 To m
        ' Assign a station
        wt.Cells(t, c).Value = ws.Cells(s, 1).Value
        ' Assign b station
        wt.Cells(t, c + 13).Value = ws.Cells(s, 1).Value
        ' Find next available slot
        Do
            ' Next column
            c = c + 2
            ' If we're past station 6 then
            If c = 15 Then
                ' Move to next row
                t = t + 1
                ' And to the column of station 1
                c = 3
                ' If we're on a break or examiner row then
                If wt.Cells(t, c).Value = "Break" Or wt.Cells(t, 1).Value = "Examiner" Then
                    ' Move to next row
                    t = t + 1
                End If
            End If
        ' Continue until we have found an empty cell
        Loop Until wt.Cells(t, c).Value = ""
    Next s
    Application.ScreenUpdating = True
End Sub

[Eng mona]

Hi Mr. HansV,

Thanks a lot for your usual help and prompt reply.
It worked perfectly as per the rules I have shared.

Could you please help me with the below?

In the attached spreadsheet, I have tabs that ready information from the timetable tab, which is populated after running the VBA code. Can you help me to link the student tab, invigilators tab and all 6 examiners tabs to the timetable in a way that if there are any manual changes done in the timetable tab (for example, names moved from some sessions to another), the same changes happen automatically in the other tabs?

Many thanks again for any provided support

[HansV]

The workbook that you attached has only two worksheets...

S2590.png

[Eng mona]

oh. my apology. I have mistakenly attached the same old spreadsheet. Here is the correct one.

[HansV]

The Student Timetable and Invigilator Timetable are easy, but I'll have to think about the Examiner sheets.

[Eng mona]

Thank you for your prompt reply.

I had rhe same thought too. I thought i can link the student and the invigilator with some sort of xlook function. But couldnt think of a way to link the examiners. Specially that there places in the timetable might change or the their initials can change too so it means at some point the tab name will also change which will break any link i could do.

I bet you will definitely come up with a better idea than mine

Looking forward to hearing from you.

Thanks in advace.

[HansV]

For what it's worth, here is the workbook with the formulas in place on the Student Timetable and Invigilator Timetable sheets. Stay tuned for the rest.

TEST 1 - Complicated timetable DRAFT.xlsm

[HansV]

And here is the complete version.
I added a formula in G1 of each of the examiner sheets to extract the initials from the sheet name. Do not alter or clear this cell!
If you change the initials of an examiner, you only need to change the corresponding sheet name, and the formula will pick up the change automatically.

TEST 1 - Complicated timetable DRAFT.xlsm

[Eng mona]

I can't thank you enough for all the help and support you have offered

The sheet code and functions are working like a charm.

[Eng mona]

Mr. Hans

Could you please explain or show steps on how to use the fomula you had in G1.

Or maybe advise on some links that explain it. I would like to learn how to use it and try to apply it in some other columns myself :)

( i was trying to link the case number column to the examiners tab but i am doing something wrong since all the cells are shoing #Split.

Many thanks in advance.

[HansV]

Before entering a formula in E1, clear E1:E51
Then enter the following formula in E1:

=IFERROR(INDEX(Timetable!D3:N10, 0, XMATCH($G$1, Timetable!C3:M3))&"", "")

This is the same as in D1, but with C3:M10 shifted one column to the right, since the case numbers are one column to the right of the student names.

Copy E1, then paste to E10, E18, E27, E35 and E44. The formula will spill to the 7 cells below.

The formula looks up the sheet name/invigilator name in C3:M3 to determine the correct column index, then returns the corresponding column from D3:N10.

[Eng mona]

Thank you so much, Mr.Hans. I have tried it, and it worked. Much appreciated.