I want the spread sheet to sort automatically by date after I'm finished
entering data. Can I set up a macro if so what do I do? If not how can I make
my data sort by date automatically when I save or close out excel?

You are going to need a 'smart' sorting routine. You could build it as a
macro that you call 'on demand' or put into one of several event handlers
within the workbook. The two most obvious event handlers would be either the
worksheet's _Deactivate() event (which would do the sort when you choose a
sheet other than the one with the data on it) or in the Workbook's
_BeforeClose() or _Open(). Since to you it is kind of irrelevant which of
those Workbook events uses it, we'll use the _Open() event later.

Here is how to get started: I would start by selecting a sheet other than
the one to be sorted and then start recording a new macro. Then go step by
step through the process: select the sheet, select the range to be sorted
(just like for the real deal) and all your sorting parameters, and do the
sort, finally choose another cell on the sheet to unselect the range and then
stop recording the macro.

This is going to give you a "dumb" macro that will sort that same area of
data everytime, so it's not going to include any added data automatically.
This is where you have to modify the code to make it smart.

Such a macro might look like this (even if it looks different, it's going to
be right for your version of Excel)...
Sub DumbSort()
Sheets("ConsolidatedList").Select
Range("D2:E13").Select
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("D2").Select
End Sub

The part you've got to make 'smart' is the Range("D2:E13").Select statement.
Assume that your dates are in column D above, you could add this code right
above that statement:
Dim LastRow as Long
LastRow = Range("D" & Rows.Count).End(xlUp).Row
then you would change the .Select statement to read like this:
Range("D2:E" & LastRow).Select

and voila! it has become a smart sort routine. The caveat on this is that
there must be nothing in the date column below the last date entered on the
sheet, otherwise you'll get more stuff to sort included than you intended.

This next part is harder to describe than to actually do. We will put the
functional part of the code into the Workbook_Open() event handler. What you
will want to copy from your 'smart' macro will be all the stuff between
Sub DumbSort()
and
End Sub

Then right-click on the Excel icon immediately to the left of the word File
in the Excel menu bar and choose View Code from the list that appears. The
VB Editor will open and show you the code module for the Workbook event code.
It will pretty much be empty. At the top of the large white area are two
narrow dropdown lists, use the one of the left [will probably say (General)
in it] and choose Workbook from the list. Immediately you should see

Private Sub Workbook_Open()

End Sub

in the large code entry area. Just paste the functional part of the code in
between those two lines. Close the VB Editor and you're ready to go. To
test it, make sure your data is NOT sorted, close the workbook, open it back
up, and you should see that it has been sorted. You could even add code
after the sort to choose a particular worksheet in the workbook and then a
particular cell on that sheet to always have the workbook open up ready to be
used on a particular sheet.

It should end up looking something like this:

Private Sub Workbook_Open()
Sheets("ConsolidatedList").Select
Range("D2:E13").Select
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("D2").Select
End Sub

Hope this helps.


"Kim T" wrote:

I want the spread sheet to sort automatically by date after I'm finished
entering data. Can I set up a macro if so what do I do? If not how can I make
my data sort by date automatically when I save or close out excel?

OOPS! I forgot to add the 'make it smart' code into the How It Should Look
example toward the end, but I think you probably realize that.

"Kim T" wrote:

I want the spread sheet to sort automatically by date after I'm finished
entering data. Can I set up a macro if so what do I do? If not how can I make
my data sort by date automatically when I save or close out excel?

Very good explanation, Jerry - it must have taken you ages to type all
that. Well done !

Pete

On May 18, 11:08 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
OOPS! I forgot to add the 'make it smart' code into the How It Should Look
example toward the end, but I think you probably realize that.



"Kim T" wrote:
I want the spread sheet to sort automatically by date after I'm finished
entering data. Can I set up a macro if so what do I do? If not how can I make
my data sort by date automatically when I save or close out excel?- Hide quoted text -

- Show quoted text -