without knowing all your ranges tad difficult. But as an idea, I turned macro
recorder on and added your example and got this:

Sub Macro1()

ActiveWorkbook.Names.Add Name:="Week_1_2009", RefersToR1C1:= _
"= Sheet1!R4C2:R7C2"

End Sub

if you copy & paste the code bit as many times as needed & updated the range
reference as required - you would then only need to run macro once to add all
the names.

Just an idea - hope helpful

--
jb


"Carrie_Loos via OfficeKB.com" wrote:

I have a list of dates associated with Week Nbrs starting in column B, C & D.
Several formulas link to a summary table on another worksheet which counts
occurances for a particular week and several years. I have figured out how to
write an If statement using data validation so that the user can see a list
of dates associated with the weekly summary labeled "Week 1" but it requires
me to define names for each week. Example Name: Week_1_2009 = $B$4:$B$7 ect...
This list, below, goes on until the end of 2012.

I was hoping to be able to write a macro that will define all these names for
me without having to physically define each name....I don't know how to do
that....Can anyone help?

Calendar Date Year Week Nbr
1/1/2009 2009 1
1/2/2009 2009 1
1/3/2009 2009 1
1/4/2009 2009 1
1/5/2009 2009 2
1/6/2009 2009 2
1

--
Message posted via http://www.officekb.com