#1
|
|||
|
|||
Closing & Opening Files
I am using the following code to create a duplicate of an active file and then close the active file. But I am struggling to open the file I just created. Will appreciate some hand holding.
Code:
Sub Copy() Dim strFilename As String strFilename = ActiveWorkbook.Path & "" & "GJCT Roster Week " & Range("sWeekNo") + 1 & " WS " & Range("sWeekStart") + 7 & ".xlsm" ActiveWorkbook.SaveAs Filename:=strFilename, FileFormat:=52 ActiveWorkbook.Close End Sub Last edited by Pecoflyer; 08-11-2019 at 11:57 PM. Reason: Replaced quote tags with code tags |
#2
|
|||
|
|||
I don't believe this is creating a duplicate of the active file.
When you save it with a different name the original will be abandon in its last saved state and you will automatically now be working with the newly named and saved file in the current state. If that is your desire, don't close the active workbook. ORAre you saying that you can't find the newly saved file after closing it ? If so, those empty double quotes may be the issue. |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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 |
#5
|
||||
|
||||
@kwhaja
Please wrap your code with code tags ( the #button) Thanks
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#6
|
|||
|
|||
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 |