Help on auto-sort (thx So much!)

[darbebo]

Hello,

Newcomer here seeking help. Using MS 2010.

My question is lets say my workbook has 2 worksheets (or tabs)
Worksheet 1 contains names (and info associated with it )that is unsorted alphabetically, i just log them as it comes in the order of which they came, for example:

Name: Submission date: Order #
Joy 1/1/2013 20130101XYY
Diana 1/1/2013 20130101XYZ
Chris 1/2/2013 20130102XYZ
Tom 1/4/2013 20130104XYZ


I am wondering if there is anyway on worksheet # 2 to have all the information on sheet 1 , BUT have the information sorted alphabetically? So basically pretending I just entered the 4th entry (Tom) in sheet 1, then in sheet 2 it will populate as :

Chris 1/2/2013 20130102XYZ
Diana 1/1/2013 20130101XYZ
Joy 1/1/2013 20130101XYY
Tom 1/4/2013 20130104XYZ

The data is sorted alphabetically with sheet 1 untouched.

I can "tried" to play with the code found on this previous post but i am no expert so i can't exactly do anything else...

http://www.mrexcel.com/forum/excel-questions/54295-auto-sort-list-values-excel.html

Can you guys help? or can this not even be done? (i foresee some sorta vlookup+macro?)

THANKS!

 

[timfritsch]

If it were me, I'd want the list to auto refresh every time the second sheet is activated. The following will do that:

Activate the Visual Basic window with Alt-F11

If the Project - VBAProject window is not visible on the left, select the menu path View, Project Explorer (or type Ctrl-R)

Find your workbook in the list and expand the Microsoft Excel Objects folder (if the folder is minimized)

Select Sheet2

In the area below the drop down box "(General)" paste the following code:

Private Sub Worksheet_Activate()
  Dim endRow As Long, endCol As Long
  
  Cells.Delete
  
  Sheets(1).Cells.Copy Sheets(2).Range("A1")
  
  endRow = Cells(Rows.Count, 1).End(xlUp).Row
  endCol = Cells(1, Columns.Count).End(xlToLeft).Column
  
  Cells(1, 1).Resize(endRow, endCol).Sort key1:=Cells(1, 1), order1:=xlAscending, _
  key2:=Cells(1, 2), order2:=xlAscending, header:=xlYes
End Sub

Hope this helps.


Tim

 

[darbebo]

If it were me, I'd want the list to auto refresh every time the second sheet is activated. The following will do that:

Activate the Visual Basic window with Alt-F11

If the Project - VBAProject window is not visible on the left, select the menu path View, Project Explorer (or type Ctrl-R)

Find your workbook in the list and expand the Microsoft Excel Objects folder (if the folder is minimized)

Select Sheet2

In the area below the drop down box "(General)" paste the following code:

Private Sub Worksheet_Activate()
  Dim endRow As Long, endCol As Long
  
  Cells.Delete
  
  Sheets(1).Cells.Copy Sheets(2).Range("A1")
  
  endRow = Cells(Rows.Count, 1).End(xlUp).Row
  endCol = Cells(1, Columns.Count).End(xlToLeft).Column
  
  Cells(1, 1).Resize(endRow, endCol).Sort key1:=Cells(1, 1), order1:=xlAscending, _
  key2:=Cells(1, 2), order2:=xlAscending, header:=xlYes
End Sub

Hope this helps.


Tim

HOLY MOLY THANK YOU SO MUCH!! IT WORKS!!! The auto-refresh is exactly what i was looking for....
Now could I ask one follow up code question:

What happens if I want........

On Sheet 2: if I want to have a specific column (call it Status) that tracks whether or not i have completed the order for that person.
So on sheet 1 i will have the raw data
On sheet 2 i will have it auto-sorted by name (which you did for me), then on maybe column D (blank column, which now i name "Status") i could manually input "Y" or "N" for status.

So basically i am trying to combine both the macro and the ability to manually input data to sheet 2. But with your code (which you are awesome to provide), everytime i enter info in sheet 1, it "refreshes" sheet 2 and all the "manual" data i entered in sheet 2 (the status column) it is erased.

You can say "well why don't you put status in sheet 1?" - i could but then the raw data is not alphabetized. And i will have to scroll up and down to locate it in order to update the status.

If i can have it auto sort and still keep the manual values i put next to it that'll be awesome....can it be done?

THANKS A MILLION!

 

[timfritsch]

That is a little more complicated. To do this, every time the 2nd sheet is activated, a new sheet is created. The data from sheet 1 is copied to this new sheet and sorted. Then we loop through the data on the new sheet and compare to the second sheet (with the status column). If the record does not exist, it is added to the second sheet. When the loop is complete, the new sheet is deleted.

Private Sub Worksheet_Activate()
  Dim i As Long, j As Long
  Dim endRow As Long, endCol As Long
  Dim test1 As String, test2 As String
  Dim newSht As Worksheet
  Dim found As Boolean
  
  Set newSht = Sheets.Add(after:=Sheets(2))
  
  Sheets(1).Cells.Copy newSht.Range("A1")
  
  With newSht
    endRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    endCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    
    .Cells(1, 1).Resize(endRow, endCol).Sort key1:=.Cells(1, 1), order1:=xlAscending, _
    key2:=.Cells(1, 2), order2:=xlAscending, header:=xlYes
    
    .Columns.AutoFit
    
    For i = 2 To endRow
      test1 = .Cells(i, 1) & "|" & Format(.Cells(i, 2), "MM/DD/YYYY") & "|" & .Cells(i, 3)
      
      found = False
      
      For j = i To Sheets(2).Range("A" & Sheets(2).Rows.Count).End(xlUp).Row
        test2 = Sheets(2).Cells(j, 1) & "|" & Format(Sheets(2).Cells(j, 2), "MM/DD/YYYY") & "|" & Sheets(2).Cells(j, 3)
        
        If test1 = test2 Then
          found = True
          Exit For
        End If
      Next j
      
      If found = False Then
        Sheets(2).Rows(i).Insert
        
        For j = 1 To 3
          Sheets(2).Cells(i, j) = newSht.Cells(i, j)
        Next j
      End If
    Next i
    
    Sheets(2).Columns("B").NumberFormat = "MM/DD/YYYY"
    Sheets(2).Columns.AutoFit
  End With
  
  Application.DisplayAlerts = False
  Application.EnableEvents = False
  newSht.Delete
  Application.DisplayAlerts = True
  Application.EnableEvents = True
  
  Set newSht = Nothing
End Sub

Tim

 

[darbebo]

Hi Tim,

thx for the answer, for some reason when i run it, sheet 2 is inaccessible ( i put the code in sheet 2) and forces me to look at sheet 3 (blank). All i have for sheet 1 is just the below.
Name Value
Chris 1
Diana 3
Tim 4
Abby 2

No worries, i think yours is already good enough, thank you so much for your help!!!! i couldn't possibly ask for more at this time!