Macro Coding Help

[Perrister]

I have a macro that I run daily, after the macro runs I manually go and update what I do not how to code.

Effectively there is information in a cell of a workbook, Cell G2, always G2. The thing is each week the file name changes by week number and the week number comes from Excel weeknum(today(),1) function.

So this week is Daily Report Week 39.xslx and next week it will be Daily Report Week 40.xslx and so on.

I know how to point to a cell in a macro but do not know how to write so account foe the week num changing? How could that be done in macro code? Any ideas? is it even possible?

Thanks in advance.

 

[Joe4]

Use this in VBA:

Application.WorksheetFunction.WeekNum(Date, 1)

 

[Perrister]

So do i write like this " Workbooks.Open Filename:="C:\Users\me\OneDrive - mycompany.com\Desktop\2024MasterWeek39.xlsx how do I get that function in this code?

Thanks Again

 

[Joe4]

Like this:

Dim fName as String
fName = "C:\Users\me\OneDrive - mycompany.com\Desktop\2024MasterWeek" & Application.WorksheetFunction.WeekNum(Date, 1) & ".xlsx"
Workbooks.Open Filename:= fName

 

[Perrister]

So elegant and easy but so not, thank you so much for your sharing your expertise and helping here, greatly appreciated. I am so embarrassed by my coding skills, but I keep learning and trying.

 

[Joe4]

You are welcome.
No need to be embarrassed! We all started out that way too!