[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Simple Excel code across workbook sheets

Posted on 2013-02-02
13
Medium Priority
?
390 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
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 31

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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 31

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 31

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 31

Accepted Solution

by:
gowflow earned 2000 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 31

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

830 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