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: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
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