Autosort by date AFTER formulas are updated

[untern8]

I have an excel report that is generated in another program every week. The report is not sorted by date and it may have 100 rows of data one week and 200 rows of data another week. My excel file has formulas that refer to this original report. I need those formulas to update first, then I need the information to automatically sort by date (column D in my excel file) and item (column c). I have tried to setup a script to run, but it's not working. Here's the script:

Option Explicit

Private Sub Workbook_Open()
    SortOnEntry
End Sub

Sub SortOnEntry()

    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Key2:=Range("C2") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
End Sub

I have a sample, but apparently don't have attachment priveleges... If you want the sample, check here:

Autosort by date AFTER formulas are updated

 

[Jerry Sullivan]

Hi Nate,

In your sample workbook, the Workbook_Open procedure was placed in a standard code module.
That needs to be moved to the ThisWorkbook module if you want it to be triggered automatically.

You could add this line to ensure that links are updated before the SortOnEntry

Private Sub Workbook_Open()
    ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
    SortOnEntry
End Sub

CurrentRegion can be used as an alternative way to select your sort range...

Sub SortOnEntry()
    Range("A1").CurrentRegion.Sort Key1:=Range("D2"), Order1:=xlAscending, 
        Key2:=Range("C2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
        MatchCase:= False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal
End Sub

Sorting fields that have formulas in them can be problematic- particularly if they have relative references instead of absolute references .

You might consider using a Query Table as a more robust approach for this.

 

[untern8]

You might consider using a Query Table as a more robust approach for this.

I like this possibility, but have zero experience with them and don't know where to start. Can you recommend a good tutorial or site that could get me started?

Thanks again for your help!

 

[Jerry Sullivan]

Nate, I notice on the other forum, you are listed as using xl2003. Is that correct, or do you have access to a later version?

Below are some instructions on how to make a query table for your purpose using xl2010 or xl2007.
For xl2003 you should be able to accomplish the same query, however I don't have access to 2003 to provide you with step by step instructions.
If needed, another member could probably identify the differences for xl2003.

From the Ribbon:
Data> From Other Sources > From Microsoft Query

In the Choose Data Source dialog:
Excel Files* > OK > (Browse to your file "Formulas for this weeks deposit2.xls" and select it) > OK

In the Query Wizard - Choose Columns:
Using the arrow buttons, Add the 6 columns you want to import
Use up/down arrows to put the columns in the desired order
Next >

In the Query Wizard - Filter Data:
Next >

In the Query Wizard - Sort Order:
Sort by Date Ascending
Then by Item Ascending
Next >

In the Query Wizard - Finish:
Click option: Return the Data to Excel
Finish

In the Import Data dialog
Click options: Table, New Worksheet
OK

That should import your table. Once this is setup, your table just needs to be refreshed to import the latest data.

I don't know of a good resource or tutorial for this. For a relatively simple query like this it's pretty intuitive once you have done it a couple times.
For more complex queries, this is a good site for learning SQL.
SQL Tutorial