Automatically adding a Date to Named Date Range Each Day - Excel 2003

[ajay_gajree]

Hi,

I have a Named Date Range in my Workbook, is there an easy VBA code to add a new (Previous Business Day) to the list each day,

I could add them in advance, but I use the range as a Data Validation List and would like to avoid showing the user future dates,

Any advise or a point in the right direction would be appreciated!

Best Regards

Ajay

 

[Trevor G]

Are the cells always going to be the same?

And do you mean name the range and add a date to the name?

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> nameMe()<br>Range("C1:C10").Name = ("Castle_" & Format(Date, "dd_mmm_yyyy"))<br><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[ajay_gajree]

The range is named, so for Example I have in the Range

01/08/11
02/08/11
03/08/11
04/08/11
05/08/11

I wish to add 08/08/11 today without going to the Range as it is on a Hidden Sheet in My Dashboard.

Just trying to automate if possible, perhaps with a macro assigned to a Button to add the previous working day to my range.

 

[Trevor G]

Try this

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> nameMe1()<br><SPAN style="color:#007F00">'Move into a Cell in the range</SPAN><br>Range("C2").Select<br>Selection.End(xlDown).Select <SPAN style="color:#007F00">'Using xlDown to move to the last cell</SPAN><br>ActiveCell.Offset(1, 0) = <SPAN style="color:#00007F">Date</SPAN> <SPAN style="color:#007F00">'Off setting 1 Cell and Adding Date</SPAN><br>ActiveCell.CurrentRegion.Name = "List" <SPAN style="color:#007F00">'Finally Selecting cells and assigning it a name</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[ajay_gajree]

Thank you Trevor, that has done the trick,

Much appreciated!

Best regards

 

[ajay_gajree]

Is it possible to use Workday instead of date?

I tried this in the code you provided, and it failed, is there a different name for this?

 

[Trevor G]

You should be able to do what you want in Excel, just by using the WORKDAY function (it is part of the Analysis Tool-Pak, make sure it is activeated by going to Tools | Add-ins and checking "Analysis Toolpak").

Also check the VBA one as well