Insert week number

[nparsons75]

I need to insert the current week number into a cell when I press a macro button (unless there is another way). I need to also enter a number into the cell as well as enter it automatically. So I will have the choice, press the button to enter the current week number or manually type the number in.

 

[Snakehips]

nparsons75,

Perhaps asign something like this to your button....

Sub Current_WN()
Range("A2") = Evaluate("WEEKNUM(TODAY(),)")
End Sub

Hope that helps.

 

[nparsons75]

Hi, sorry to be a pain, how do I assign code to a button. (the button is just a shape I created) Do I need to insert a button? Apologies for sounding a bit naff, I have created buttons in VB but never into the spreadsheet, i guess very straight forward.

 

[Snakehips]

Open the vb editor e.g. Alt F11 or right click sheet tab and View Code.

In the left hand project pane, double click on your desired sheet and paste the code into the white space code pane on the right.

Then in the worksheet, right click on your shape and select Assign Macro.
Select the Current_WN macro from the list and click OK

 

[nparsons75]

Hi I managed to create the button but get a compile error when running.

 

[Rick Rothstein]

nparsons75,

Perhaps asign something like this to your button....

Sub Current_WN()
Range("A2") = Evaluate("WEEKNUM(TODAY(),)")
End Sub

Hope that helps.

Just pointing out that you can use native VBA functions to do the line I highlighted in red instead of having the Evaluate function reach out to the worksheet WEEKNUM and TODAY functions to do it. This line of code will work the same as the red highlighted one...

Range("A2") = DatePart("ww", Date)

 

[nparsons75]

Absoloutly fantastic guys, thanks to both of you.

I used this code:

Sub Current_WN()
Range("F5") = DatePart("ww", Date)
End Sub

Works a treat, thank you.

 

[nparsons75]

Hi, for some strange reason this code has stopped working, yesterday worked perfectly but now it will not change the week number in the field, it flashed as if it did something but the field did not change.

 

[Snakehips]

If you are running Excel 2007 or newer, did you save the file as MacroEnabled?
If you right click the button - Assign Macro can you still see the Macro in the list?

 

[Rick Rothstein]

Hi, for some strange reason this code has stopped working, yesterday worked perfectly but now it will not change the week number in the field, it flashed as if it did something but the field did not change.

Here in the US, yesterday was Tuesday and today is Wednesday... they are both in the same week so the week number should not change.