You are spot on with your observation. When I removed close code it worked. following is the code I am using now.
Sub Copy()
Dim strFilename As String, D As Workbook ', K As Workbook
Set D = ActiveWorkbook
strFilename = D.Path & "\GJCT Roster Week " & Range("sWeekNo") + 1 & " WS " & Range("sWeekStart") + 7 & ".xlsm"
D.SaveCopyAs Filename:=strFilename
Workbooks.Open (strFilename)
D.Close
End Sub
Only one thing I am now confronted with is the need to replace named ranges to the values I am renaming the file to in the above statements (sWeekNo and sWeekStart). In nutshell I would like the newly created file to show the new week number and week start date as reflected in the file name. Could the be extracted from file name?
|