Solved

Simple Excel code across workbook sheets

Posted on 2013-02-02
13
285 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
  • 6
  • 5
  • 2
13 Comments
 
LVL 14

Expert Comment

by:Faustulus
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
Can you post the workbook ?
gowflow
0
 

Author Comment

by:imij11
Comment Utility
here's a copy of the workbook.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
u forgot to post it !!! :)
gowflow
0
 

Author Comment

by:imij11
Comment Utility
sorry about that
2013-Speller-List.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

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

Expert Comment

by:Faustulus
Comment Utility
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
Comment Utility
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 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now