Closing & Opening Files

NoSparks · #1 08-13-2019, 04:04 AM

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

Khwaja · #2 08-14-2019, 10:04 PM

I am very grateful for your assistance. It works great and in the manner I envisaged. You are a star.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Emails disappear after closing and re-opening outlook	Rhen	Outlook	1	01-07-2019 01:16 AM
Track Changes Automatically Accepting Changes after Closing and Re-Opening Document	cnethercott	Word	1	01-16-2017 03:21 AM
Closing Word files	Humptydingo	Word	5	09-07-2013 10:17 AM
Update powerpoint on a server without closing an opening	dntel123	PowerPoint	0	08-18-2011 02:41 AM
WORD opening and closing is slow	chum	Word	2	07-05-2010 07:06 AM