Help with macro

[chobanne]

Hello all i have a macro like shown bellow. Can someone please add me the lines of code that would somehow remember the original file name, close the renamed file after renaming, and reopen the original file. It would be even better if it is possible to save file as new one without closing and reopening the original one. Thank you

Sub Save_as()

Dim i As Variant
Dim fPath As String

i = InputBox("New name", "Save as")

Range("BN2").Value = i

Dim ans As VbMsgBoxResult
ans = MsgBox("Add more description", vbQuestion + vbYesNo)
If ans = vbYes Then
fPath = ThisWorkbook.Path & Application.PathSeparator
ThisFile = fPath & Range("BO2").Value
ActiveWorkbook.SaveAS Filename:=ThisFile
Else
fPath = ThisWorkbook.Path & Application.PathSeparator
ThisFile = fPath & Range("BP2").Value
ActiveWorkbook.SaveAS Filename:=ThisFile
End If

End Sub

 

[dmt32]

Hi
try using SaveCopyAs method which will save a copy of the workbook to a file but does not modify the open workbook in memory

change this

ActiveWorkbook.SaveAS Filename:=ThisFile

to this

ActiveWorkbook.SaveCopyAs Filename:=ThisFile

Dave

 

[chobanne]

Thank you, that's works nice, but can you help me in a way to make macro to do like this. To remember the original file name, close the renamed file , and reopen the original file after that. Because i would like to add some lines of code before closing the renamed file. That changes will only been seen in renamed file.

 

[dmt32]

Thank you, that's works nice, but can you help me in a way to make macro to do like this.

To remember the original file name, close the renamed file

SaveCopyAs’ method of workbook object saves a copy of the workbook as a file and does not change the original filename

and reopen the original file after that.

the open workbook is not closed or changed when using the SaveCopyAs method


Dave

 

[RustamMika]

Hi chobanne,​

Try this code:
It will make the copy of your existing file in same folder

Sub Save_as()
    old_name = ThisWorkbook.Path & Application.PathSeparator & ThisWorkbook.Name
    new_file_name = InputBox("New name", "Save as")
    new_name = ThisWorkbook.Path & Application.PathSeparator & new_file_name & ".xlsx"
    With CreateObject("Scripting.FileSystemObject")
        Call .copyFile(old_name, new_name)
    End With
End Sub

 

[chobanne]

RustamMika i understand your thoughts, but because i added some line in your macro i got new file with changes. The file with changes stays open, but the original file is just saved.
Actually i need to be different. The original file must stay opened and the file with changes to be just saved in same directory. Can you change the code somehow.

 

[chobanne]

Or maybe to tell my where to put my new lines of code, to take a effect on file that will be just saved

 

[Alex Blakenburg]

It looks you may have worked out that Dave's @dmt32 code will work for.
If you need more information and some sample code Bill Jelen covers it here.
Note: you can't change the file type (extension) using savecopyas which Bill addresses in his code.

Save As Keeping Original Open

How an Excel VBA Macro can save copies of the workbook while keeping the workbook open

www.mrexcel.com

 

[dmt32]

@Alex Blakenburg - Interesting article thanks for sharing.
I shared a solution Here I created some while ago for others with similar requirement but not fully sure if it resolved their issues as had no feedback.

Dave

 

[Alex Blakenburg]

@Alex Blakenburg - Interesting article thanks for sharing.
I shared a solution Here I created some while ago for others with similar requirement but not fully sure if it resolved their issues as had no feedback.

Dave

You pretty much covered it all in that post