Help with a pulling Month info Macro...PLEASE

I'm pretty new to Macros so this may be a quick fix for one of you experts.
I have row A1:A12 with all the months Jan-Dec
I have row B1:B12 with the numbers 100 in each row (if you add them all up you'll have 1200)

What I'm attempting to do the following..... Based off what month it is (Example we're in May), I want excel to only count A1:A5 and so on.
So if this was November, excel would reconize it is November and count A1:A11.

I wrote a Function called "ThisMonth()", this is my first function.
It works, however..... it only appears to work when I play the macro.
If when in Excel I type =ThisMonth() I get a #NAME? error. I'm not sure how to correct this. Can anyone assist please?

This is how I wrote the function.
FYI.... The answer box is in the 21st row, Column 3 (you'll see it in the activesheet call)

-----------------------------------------------
Public Function ThisMonth()


Select Case Month(Date)

Case 1
mytotal = Application.Sum(Range("A1:A1"))
ActiveSheet.Cells(21, 3) = mytotal
Case 2
mytotal = Application.Sum(Range("A1:A2"))
ActiveSheet.Cells(21, 3) = mytotal
Case 3
mytotal = Application.Sum(Range("A1:A3"))
ActiveSheet.Cells(21, 3) = mytotal
Case 4
mytotal = Application.Sum(Range("A1:A4"))
ActiveSheet.Cells(21, 3) = mytotal
Case 5
mytotal = Application.Sum(Range("A1:A5"))
ActiveSheet.Cells(21, 3) = mytotal
Case 6
mytotal = Application.Sum(Range("A1:A6"))
ActiveSheet.Cells(21, 3) = mytotal
Case 7
mytotal = Application.Sum(Range("A1:A7"))
ActiveSheet.Cells(21, 3) = mytotal
Case 8
mytotal = Application.Sum(Range("A1:A8"))
ActiveSheet.Cells(21, 3) = mytotal
Case 9
mytotal = Application.Sum(Range("A1:A9"))
ActiveSheet.Cells(21, 3) = mytotal
Case 10
mytotal = Application.Sum(Range("A1:A10"))
ActiveSheet.Cells(21, 3) = mytotal
Case 11
mytotal = Application.Sum(Range("A1:A11"))
ActiveSheet.Cells(21, 3) = mytotal
Case 12
mytotal = Application.Sum(Range("A1:A12"))
ActiveSheet.Cells(21, 3) = mytotal
Case Else
MsgBox ("Not working")

End Select
End Function

hi AceForSale, welcome to ExcelForum, this can be done with a simple formula, please check attachment

Under the Forum Rules you need to use special tags around the posted code: http://www.excelforum.com/forum-rule...rum-rules.html

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Select your code and click the [#] button at the top of the post window (if you are editing an existing post, press Go Advanced to see the [#] button). The result will appear like this in the post window:

Please Login or Register  to view this content.

Thank you it worked perfectly.
I'm sorry about not placing the code tags, I know now how to do it future reference thanx to your assistance. Have a great day.
Dave