Running Macro when Excel file is opened?

[mattborn]

In my Excel workbook I have a Date column.
I need a macro that runs when the workbook is opened.
It needs to do the following:
1. Sort the Date in Ascending order (I can do this)
2. Go to the Date cell that has the closest date to today's date (current date)

I can get the macro to sort, but I can't get it to do #2 or have it run when the workbook is opened.

Thanks

 

[dan1967]

Since you are sorting the dates in ascending order, you should just be able to select the last date in the list:

set c = range("a1")
do while not isempty(c)
     set c = c.offset(1,0)
loop
c.offset(-1,0).select

This should work for you.


Dan.

 

[JPMontreal]

Hi,

First, put your macro in the even Workbook_open of ThisWorkbook module.

Second, Add this code at the end of your macro (put the right worksheet and the good range)

Dim rg As Range
Worksheets("sheet1&quot.Activate
ActiveSheet.Range("d1&quot.EntireColumn.Select
For Each rg In Selection
If rg.Value >= Date Then
rg.Select
Exit For
End If
Next rg


Jean-Paul
Montreal
jp@solutionsvba.com
mtljp2@sympatico.ca

 

[SkipVought]

Hi,

Use the Workbook_Open event to fire whatever procedure you want.

=MATCH(TODAY(),DateList,1)

will identify the row offset in DataList range that is equal to or less than today.

=INDEX(DateList,MATCH(TODAY(),DateList,1),1)

returns that date

=INDEX(DateList,MATCH(TODAY(),DateList,1)+1,1)

returns the next date in the list.

Compare these two and you have your answer.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[mattborn]

JPMontreal,
That works perfectly!

One more thing if any of you know how its done.
My worksheet uses 'freeze panes' so that the field names are always at the top.
Is there a way so that the chosen date is scrolled so that it appears right under the freeze pane?