Solved

Simple Excel code across workbook sheets

Posted on 2013-02-02
13
369 Views
Last Modified: 2013-02-03
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
0
Comment
Question by:imij11
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
13 Comments
 
LVL 14

Expert Comment

by:Faustulus
ID: 38847864
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
 
LVL 30

Expert Comment

by:gowflow
ID: 38848602
Can you post the workbook ?
gowflow
0
 

Author Comment

by:imij11
ID: 38848760
here's a copy of the workbook.
0
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 30

Expert Comment

by:gowflow
ID: 38848796
u forgot to post it !!! :)
gowflow
0
 

Author Comment

by:imij11
ID: 38848915
sorry about that
2013-Speller-List.xlsm
0
 
LVL 30

Expert Comment

by:gowflow
ID: 38849075
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
 

Author Comment

by:imij11
ID: 38849101
I suppose that would be a more thorough approach, so yes that statement is correct.
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 38849102
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
 

Author Comment

by:imij11
ID: 38849235
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
 
LVL 30

Accepted Solution

by:
gowflow earned 500 total points
ID: 38849284
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
 

Author Comment

by:imij11
ID: 38849345
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
 

Author Closing Comment

by:imij11
ID: 38849353
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
 
LVL 30

Expert Comment

by:gowflow
ID: 38849909
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

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question