Insert week number

nparsons75

Well-known Member
Joined
Sep 23, 2013
Messages
1,247
Office Version
  1. 2016
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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,592
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
nparsons75,

Perhaps asign something like this to your button....
Code:
Sub Current_WN()
Range("A2") = Evaluate("WEEKNUM(TODAY(),)")
End Sub

Hope that helps.
 

nparsons75

Well-known Member
Joined
Sep 23, 2013
Messages
1,247
Office Version
  1. 2016
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

Well-known Member
Joined
May 17, 2009
Messages
5,592
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Sep 23, 2013
Messages
1,247
Office Version
  1. 2016
Hi I managed to create the button but get a compile error when running.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
38,020
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
nparsons75,

Perhaps asign something like this to your button....
Rich (BB code):
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

Well-known Member
Joined
Sep 23, 2013
Messages
1,247
Office Version
  1. 2016
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

Well-known Member
Joined
Sep 23, 2013
Messages
1,247
Office Version
  1. 2016
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

Well-known Member
Joined
May 17, 2009
Messages
5,592
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
38,020
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,171,579
Messages
5,876,290
Members
433,192
Latest member
butterexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top