Macro to define range names

[Carrie_Loos via OfficeKB.com]

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

 

[john]

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

 

[Carrie_Loos via OfficeKB.com]

I was hoping for something not so labor intensive but I did end up using your
suggestion. Thanks

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

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

[quoted text clipped - 16 lines]

1/6/2009 2009 2
1

 

[jaf]

Hi Carrie,
Excel has a weeknum() function in the analysis toolpak addin. (tools>addins...)
There are also several ISOweeknum functions. A google search should bring up several dozen.

John