Auto-Sorting After New Input On Different Worksheet

[CJoshuav]

I realized that my original post was unclear, but could not find an "Edit" button for my previous post. Mods, can you delete the original thread? It has no comments. Thanks!

First, I apologize for another auto-sort thread. I read through several of the various examples, and still wasn't able to reverse engineer exactly what I need.


Here's the setup:


I have daisy-chained three worksheets together: Data Entry / Data Sorting / Data Crunching


In 'Data Entry' the user enters date, time, and event information, all of which gets pulled into 'Data Sorting'


In 'Data Sorting' the event is assigned a number and then sorted, Descending, by date and then time. That gets pulled into 'Data Crunching'


In 'Data Crunching', all kinds of fun and creative things happen to the sorted data.


I'm using three sheets because I want to keep the data entry sheet unlocked for entry, and when I tried pulling it directly into the data crunching sheet and sorting I ended up shuffling all the formulas around. (If I were to hard-reference the formulas using $'s, I wouldn't be able to auto-populate the thousands of cells with click-dragging.) (Manually) sorting on one sheet and then pulling into the next for crunching has worked fine.


I have a two questions, that I'll number below.


1. I'd like to sort by one of two means:


- Every time new data is entered into 'Data Entry', have columns B through M on 'Data Sorting' sort by column B, descending. If possible, it would be great if they could then sort by Column C, but - as the numbers currently crunch - it shouldn't matter.


or


- Give the user a button they can push and sort everything, as above.


Here's the code I tried in "ThisWorkbook"

Private Sub Workbook_Open()
    ActiveWorkbook.Worksheets("Data Sorting").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Data Sorting").Sort.SortFields.Add Key:=Range("A5:A100") _
    , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Data Sorting").Sort
        .SetRange Range("B1:M1000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

I get the feeling that this is close, but that at the beginning I need something about Worksheet_Change and the 'Data Entry' worksheet?


2. Also, and related to this, I had to put

=IF(ISBLANK('Data Entry'!B2),"",'Data Entry'!B2)

in each of the Address cells (column B) on the 'Data Sort' worksheet because otherwise the 0 value was returning January 1, 1900. Unfortunately, on a descending sort, all of my ""s are moved to the top.


Is there a quick fix for this? Can it be built into the same macro?


Thanks very much for your patient help!