Sorting over multiple sheets

[primaryteach]

Hi all,

This is my first post - so please treat me kindly.
I'm a primary school teacher in England and I am trying to use Excel to create a mark book.
I want to use a sheet for each subject, rather than put the grades all on one sheet but this leads me to the problem I need assistance with.

When new pupils are added, I would like to be able to sort the data alphabetically. However, I have referenced data from the first page in formulas on subsequent pages, to save time copying and pasting or retyping (e.g. pupil names). So when I sort my first sheet, it automatically sorts the referenced data on the subsequent sheets but not the rest of the data (e.g. grades), so they all get in a muddle.

So my question is: Can you use VBA to sort all the sheets together at the same time, so when the referenced data changes on the first sheet, all data on other sheets automatically is sorted?

I hope that makes sense. I have attached a mock file to this post, so you can see how I am setting it up at the moment. The final workbook will have a much larger amount of information and therefore I feel putting all the data on one sheet to make the sorting process easy will too cumbersome to navigate.

Thanks for your help,

Simon

[Bob Phillips]

Try this



Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:A"
Dim SavedValue As String
Dim ws As Worksheet
Dim LastRow As Long
Dim NumRows As Long
Dim MatchRow As Long

On Error GoTo ws_exit
Application.EnableEvents = False
'Application.Calculation = xlCalculationManual

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
SavedValue = .Value
LastRow = Me.Cells(Me.Rows.Count, "A").End(xlUp).Row
Me.Cells(LastRow, "A").Value = .Value
Me.Rows(1).Resize(LastRow).Sort key1:=Me.Range("A1"), _
order1:=xlAscending, _
header:=xlYes
MatchRow = Application.Match(SavedValue, Me.Columns(1), 0)
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> Me.Name Then
ws.Rows(MatchRow).Insert
ws.Cells(MatchRow, "A").Formula = "='" & Me.Name & "'!A" & MatchRow
ws.Cells(MatchRow, "B").Formula = "='" & Me.Name & "'!B" & MatchRow
ws.Cells(MatchRow, "C").Formula = "='" & Me.Name & "'!C" & MatchRow
NumRows = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row - MatchRow + 1
ws.Cells(MatchRow, "A").Resize(, 3).AutoFill ws.Cells(MatchRow, "A").Resize(NumRows, 3)
End If
Next ws
End With
End If

ws_exit:
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[primaryteach]

XLD, thanks for the code. Unfortunately, it keeps going to the debugger with a compile error when I try to add a new pupil or grade. What am I doing wrong?

Simon

[Bob Phillips]

Simon,

Can't think what is causing that.

Take a look at this and compare what yours is like,

[primaryteach]

Thanks XLD this works really well, but I have discovered a new problem. I want to protect certain parts of the sheets but when I do so, the above code stops working. Is there a way to use the above code but still have the sheets protected?

Thanks for your time,

Simon

[Bob Phillips]

Just add some code to unprotect the sheets before the main code kicks in, protect it afterwards.

[primaryteach]

Sorry, I am a complete noob to VBA. What should the code look like to unprotect at the beginning and protect at the end?

[Bob Phillips]

Me.Unprotect "password"


at the start, and


Me.Protect "password"


at the end, in the ws_exit section.

[primaryteach]

Hi, that works really well - thanks again. Is it possible to have two rows at the top that don't change, i.e. row a and b, but still apply the reordering code to the names below. If so, how do I adjust the code posted earlier?

Simon

[Bob Phillips]

Not tested, but I guess you would change



Me.Rows(1).Resize(LastRow).Sort key1:=Me.Range("A1"), _
order1:=xlAscending, _
header:=xlYes


to



Me.Rows(2).Resize(LastRow - 1).Sort key1:=Me.Range("A2"), _
order1:=xlAscending, _
header:=xlYes

[primaryteach]

Hi XLD,

You are helping me so much, that I feel awful that I have still got problems.

I have decided to include a more up to date version of the spreadsheet as an attachement. It is going to have a load more worksheets but I want to get that it right before moving further foward.

I have now found that when I add a name of the front sheet it sorts that sheet out correctly, but the names and grades on the next 'target' sheet are misaligned by one.

Also I get an 'unprotect' dialogue box when returning from the second sheet to the first (which I don't want - I just want the sheets to remain protected).

Could you take a look to see what a mess I am making of this and suggest changes.

Thanks

Simon