Simple Excel code across workbook sheets

Hi Experts,

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
End If
End Sub
imij11Asked:
Who is Participating?
 
gowflowConnect With a Mentor Commented:
imij11

Is this what you want ?? I disabled your macro. whatever you do in any of the sheets will be replicated in the combined. Try putting just last name and go check then first name thenmodify a name then modify a grade or whatever.

Let me know if this is what your looking for.

As a small note to Faustulus we ought to answer user's request as they best know how they want things although sometime certain logic  may prove otherwhyse, we only know a small portion of what the user know about his biz or his requirements.

Rgds/gowflow
2013-Speller-List.xlsm
0
 
FaustulusCommented:
Pardon me!
But your reason for wanting to save effort is because your workflow is inefficient. Building on the faulty foundation doesn't promise anything good for the future.
Instead of five lists you should have only one which you can sort alphabetically or by date of entry or by age of student or whatever columns you have to sort on.
One extra column you should add, and that is the school's name. Now you can filter on that column to create the four other lists any time you want, ready sorted - all without code.
Wouldn't that be more efficient?
0
 
gowflowCommented:
Can you post the workbook ?
gowflow
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
imij11Author Commented:
here's a copy of the workbook.
0
 
gowflowCommented:
u forgot to post it !!! :)
gowflow
0
 
imij11Author Commented:
sorry about that
2013-Speller-List.xlsm
0
 
gowflowCommented:
ok fine, you said:
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.
>>> What if the row is deleted ?
What if the row is modified ?

I guess your answer would be: In all cases addition, deletion, modification I want the data to be replicated in the last sheet and sorted alphabetically.

If this statement is correct pls advise case not also clarify.
gowflow
0
 
imij11Author Commented:
I suppose that would be a more thorough approach, so yes that statement is correct.
0
 
FaustulusCommented:
See what I mean?!
Maintaining sync of 4 worksheets with a fifth entails a lot of code. The more the code the bigger the room for errors and exceptions. As you continue to develop your system this ballast will only increase. It will become unmanageable very quickly.
The four lists you need are furnished by Excel automatically. You don't need to maintain them. Therefore you also don't need to think about additions, deletions and modifications.
0
 
imij11Author Commented:
I'm not sure I follow how they are furnished automatically? Following what you suggest I don't  see how it's possible to come up with a way to sort alphabetically and by school simultaneously in one spreadsheet. I do understand that the more code involved the larger the  margin for error. There won't be any other variables introduced in the sheets. I really just wanted to minimize a duplicate entry process. I don't foresee the sheets getting very large either.
0
 
imij11Author Commented:
gowflow this is wonderful. It's working perfectly. Thank you so much this is exactly what I needed. I see that the code is still pretty advanced for what on the surface seems a simple task. Again thanks you for your help.
0
 
imij11Author Commented:
My expectation was to get some code samples that I could modify for my purpose. A pointer in the right direction. Gowflow went far beyond that and created the code for me. This is well above the call of duty and I greatly appreciate it.
0
 
gowflowCommented:
Thank you very much for the very nice appreciation. It is my pleasure to deliver exactly what you requested. Pls feel free to let me know if you need further help on any other issue.
gowflow
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.