How to safe a file with calendar week in the name (WeekNum function)

[Martin09]

Hello,

I am creating a simple macro with a part where the file gets saved with the current calendar week and a name from cell B2:

Path = "P:\Martin\LT"
filename = "LT CW02 " & Range("B2")
ActiveWorkbook.saveAs filename:=Path & filename & ".xlsm", FileFormat:=xlNormal


Above code works and it gets saved correctly, but every week I will have to change the code to the current calendar week. Is there a way how to change it so that I can use WeekNum function? I tried following but does not work:

filename = "LT CW" & WeekNum(TODAY(), 21) & Range("B2")

Probably I cannot use normal Excel functions in VBA code, but I am not sure how to work it out, as I have only basic knowledge of VBA so far.

Thank you for help.

Martin

[thomglea]

Good news! You CAN use normal Excel functions in VBA

filename = "LT CW" & WeekNum(TODAY(), 21) & Range("B2") translated to working VBA is below. Change SheetName as needed.

filename = "LT CW " & Application.WeekNum(Date, 2) & " " & ThisWorkbook.Sheets("SheetName").Range("B2")

[Martin09]

wow. Thank you for your fast reply, I will try it next week at work and will let you know..
Have a good day