Solved

Simple Excel code across workbook sheets

Posted on 2013-02-02
13
306 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 29

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
 
LVL 29

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 29

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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 29

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 29

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

914 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

19 Experts available now in Live!

Get 1:1 Help Now