Excel 2010 Auto sort

1 workbook
2 worksheets - Labels and Names

Worksheet2 gets data from worksheet1 thusly:
on Worksheet 2 (Being "Names")   A3 =Labels!A3, A4 =Labels!A4, etc ...
This list on 'Labels' will continue to be added to so I would like to do this open ended if possible, where entering data on the 'Labels' Worksheet auto-populates on the 'Names' worksheet and also automatically alphabetizes, each time they add an entry on 'Labels'

Additional Functionality:
On the 'Names' worksheet each row will have several columns of information associated with it, so I really need it to sort the rows, based on the 1st column, so columns a -  j for example, based on a
Who is Participating?
SiddharthRoutConnect With a Mentor Commented:
alexianit: Here is an alternative. Please read the below before opening the file. This is the best alternative that I could think of for sorting in your case. Let me know if you have any questions.

When you open the workbook, you will notice that I have entered some sample data in Sheet "Names".

What the code does is

1) The moment you enter any value or make any changes in Col A of Sheet "Labels", it
creates a blank sheet and copies all the data from the "Names" Sheet and pastes it there.
2) It then clears the "Names" Sheet
3) Sorts Col A-D in Sheet "Labels"
4) Copies the sorted values to sheet "Names" and repopulates the respective Col B-M from the temp sheet
5) Deletes the temp sheet.

Please note that I have removed all the formulas from the Sheet "Names" as it is not required any more.

Hope this helps?


Code Used

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lastRowAs As Long, lastRow As Long, i As Long
    Dim ASheet As Worksheet, ws As Worksheet, ws1 As Worksheet
    Set ASheet = Sheets("Labels")
    Set ws1 = Sheets("Names")
    lastRowAs = ASheet.Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    On Error GoTo Whoa
    If Not Intersect(Target, Range("A2:B" & lastRowAs)) Is Nothing Then
        Application.EnableEvents = False
        Set ws = Sheets.Add
        ws.Name = "Temp"
        lastRow = ws1.Range("A" & Rows.Count).End(xlUp).Row
        ws1.Range("A3:M" & lastRow).Copy
        ws.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        ASheet.Range("A2:D" & lastRowAs).Sort _
        Key1:=ASheet.Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        ws1.Rows("3:" & lastRow).ClearContents
        ASheet.Range("A2:A" & lastRowAs).Copy _
        For i = 3 To lastRow
            strSearch = ws1.Range("A" & i)
            Set acell = ws.Columns(1).Find(What:=strSearch, LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)

            If Not acell Is Nothing Then
                ws.Range("B" & acell.Row & ":M" & acell.Row).Copy
                ws1.Range("B" & i).PasteSpecial Paste:=xlPasteValues, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            End If

        Next i
        Application.DisplayAlerts = False
        On Error Resume Next
        On Error GoTo 0
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
        Application.EnableEvents = True
        Set ASheet = Nothing
        Set ws = Nothing
        Set ws1 = Nothing
    End If
    Exit Sub
    MsgBox Err.Description
    Resume LetsContinue
End Sub

Open in new window

alexianit, I have a theory aroud it but before I comment may I see a sample file?

alexianitAuthor Commented:
I've created a dummy file that should help explain the columns and sheets I'm working with.
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Ok Upon rereading your post and comparing it with the demo file I see you don't have any data in the Sheet "Names" in column B to J? Could you confirm if there will be any data in those columns?

alexianitAuthor Commented:
Try this one.
alexianit: Ok, I saw the file and was able to create a basic sort but here is a quick question. Where are you picking the values of Col B to M in Sheet "Names"?

The logic that I am following if to sort the data in Sheet "Labels" automatically when you enter the data in the Sheet "Labels" This ensures that the list in both the sheets in Col A are in Proper Case and are sorted. The only challenge is the columns in Sheets "Name".

alexianitAuthor Commented:
information in those columns is entered manually
alexianitAuthor Commented:
Good answer.
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.