View Single Post
 
Old 08-13-2019, 04:04 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 842
NoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of light
Default

Use variables, something along the lines of this.
Had to format the date to eliminate forward slashes which are illegal in the file name.
Code:
Sub Try_This()

    Dim strFilename As String, D As Workbook
    Dim newWeekNo As String, newWeekStart As String

Set D = ActiveWorkbook

newWeekNo = Range("sWeekNo") + 1
newWeekStart = Format(Range("sWeekStart") + 7, "d-mmm-yy")

strFilename = D.Path & "\GJCT Roster Week " & newWeekNo & " WS " & newWeekStart & ".xlsm"

D.SaveCopyAs Filename:=strFilename

Workbooks.Open (strFilename)

Range("sWeekNo") = newWeekNo
Range("sWeekStart") = newWeekStart

D.Close

End Sub
Reply With Quote