Simple Excel code across workbook sheets
Posted on 2013-02-02
I have an Excel workbook that contains 5 sheets. The first 4 sheets are a list of the last name, first name, and grade of the students from 4 different schools. The 5th sheet is an alphabetical list of the students from all 4 schools. I would like to create a code so when a row is added to any of the first 4 sheets it is automatically added to the 5th sheet and sorted alphabetically.
I was able to piece together a code on the 5th sheet that message prompts to resort when a row is manually added (using the code below) but I have not been ale to get it to work when trying to link the sheets. Any help with some code would be greatly appreciated.
code for alphabetical sort:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("C:C")) Is Nothing Then
response = MsgBox("Do you want to sort", vbYesNo)
If response = vbNo Then Exit Sub
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row
Range("A2:F" & Lastrow).Sort Key1:=Range("A2"), Order1:=xlAscending