I am trying to create a macro to open a file that's name changes each week. The file name is Labor - Week 7 - 1st.xls. Each week it will be a new number: 8, 9, 10, .... and so on. I have a macro that will open it for Week 7, but I don't know what to change.

Sub DailyUpdate()
'
' DailyUpdate Macro
'

'

Workbooks.Open Filename:= _
"\\spartanburg\SHARED\Production\Productivity\2015 Production Weekly\Labor - Week 7 - 1st.xls"
Windows("Working attendance balance.xlsx").Activate

End Sub






I also need to make a second macro that will find and replace the worksheet from one week to the next. Like find and replace week 6 with week 7, based on the current week it is. Just like above it changes each week. This is what I have so far.

Sub UpdateWeek()
'
' UpdateWeek Macro
'

'
Range("N11").Select
Cells.Replace What:="Week 6", Replacement:="Week 7", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("O4").Select
ActiveCell.FormulaR1C1 = "6"
Range("O6").Select
Cells.Replace What:="Week 7", Replacement:="Week 6", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub


Please help. Thank you in advance.