Save a copy of a workbook with a new name

[AndyTampa]

I have two workbooks that I open every day. Workbook #2 relies on cells in workbook #1 . Occasionally, I need to save Workbook #1 with a different name, but when I "save as", it changes the relationship source for cell references in workbook #2 .

What I need to be able to do is run a macro that will save the current workbook to a specific directory but ask me for a new name. It needs to do this without changing the name of Workbook #1 and breaking the cell references in Workbook #2 .

I've found stuff for ActiveWorkbook.SaveCopyAs, but it requires a file name in the code. I don't know how to create the dialog box to ask me which name and then insert it in the command with a specific path. I'd also like the dialog box to display this path.

It sounds simple, but I'm not familiar with VBA. I'm good enough to copy, paste, and occasionally edit.

Can someone help me please?

 

[DataBlake]

Re: Need to save a copy of a workbook with a new name

just replace "[book1]" with the name of the original workbook 1

Sub referenceFIX()
Dim wsCOUNT As Long
Dim i As Long
Dim newWB As String

newWB = InputBox("What is the name of the new workbook?")
wsCOUNT = ActiveWorkbook.Worksheets.Count

For i = 1 To wsCOUNT
ActiveWorkbook.Worksheets(i).Cells.Replace What:="[" & newWB & "]", Replacement:="[Book1]", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Next i


End Sub

 

[AndyTampa]

Re: Need to save a copy of a workbook with a new name

I tried the macro you wrote. It asked me for the name of a new file, which I entered, but I can't find that file saved anywhere. You asked that I put the name I want in [book1] in the macro. I put the name of the workbook "ALL-IN-1 CALCULATOR 2019 with PTD.xlsm". That is the name of WB1 and it is a Read-Only file.

Trial 1) I ran the macro from within WB1. It asked for a name so I named it runfromWB1.xlsm. Then the macro simply ended. I checked Documents, My Documents, Public Documents, and the original location of WB1, but find no file.

Trial 2) I ran the macro from within WB2, naming it runfromWB2.xlsm, and got the same result.​

Trial 3) I first SaveAs WB1 as Test Save.xlsm. My open document now says Test Save.xlsm and my cell references in WB2 all reference Test Save.xlsm. I ran the macro from within Test Save.xlsm. The macro asked me for a new name, so I tried runfromRenamedWB.xlsm and the macro ended. I still have Test Save.xlsm and WB2 references Test Save.xlsm.​

Trial 4) I first SaveAs WB1 as Test Save.xlsm. Again, my open document now says Test Save.xlsm and my cell references in WB2 all reference Test Save.xlsm. I ran the macro from within WB2. I got the same result as Trial 3.​

Trials 5 and 6 involved using the Test Save.xlsm name in the dialog box. Trial 5 was run from within Test Save.xlsm. The macro did nothing. Trial 6 was run from within WB2. This time I got a different result. After entering Test Save.xlsm in the dialog box, the macro opened up a directory for me to find a file. It did this three times, once for each find of the Test Save.xlsm reference "in brackets". It did not replace the references to named ranges that didn't have brackets. It also left WB2 referencing a document that was no longer in Excel because it was saved with a new name.​

I'm not an expert with macros, but this doesn't appear to do what I need. If I'm understanding this right, even if I get the macro to change all the references back, I still need to open an original WB1 again, which is one of the things I'm trying to avoid.

I only want to save a copy of WB1 in the background without changing the name of the worksheet currently displayed. WB2 is not relevant to the copy. This macro you've written is specific to only fixing WB2. If I can get a copy of WB1 made, there will be no reason to fix WB2. A macro that saves a copy would also be quite a bit more useful as it would work on any Excel workbook.

I appreciate you trying to help, but it appears you are steering me in a direction in line with your impression of what I should need. I just want to save a copy of a workbook to a folder for future use. I need to leave the original upfront and unchanged while I am working.

VBA even has a function SaveCopyAs, but I can't get it to work properly. I got it to save files that couldn't be opened. Can we pursue what I posted earlier and see if that bears any fruit? From what I've read online, similar things are being done but it's all just a hair beyond my level of knowledge.

 

[DataBlake]

Re: Need to save a copy of a workbook with a new name

I still need to open an original WB1 again, which is one of the things I'm trying to avoid.

then what is the file path for the ALL-IN-1 CALCULATOR 2019 with PTD.xlsm
i.e c:/users/desktop/folder/

 

[DataBlake]

Re: Need to save a copy of a workbook with a new name

With the original workbook 1 being closed
The copy of workbook 1 with the new name open and saved
and the 2nd workbook (where the macro will be located in) open

try this code:

Sub referenceFIX()
Dim wsCOUNT As Long
Dim i As Long
Dim newWB As String

newWB = InputBox("What is the name of the new workbook?")
wsCOUNT = ActiveWorkbook.Worksheets.Count

For i = 1 To wsCOUNT
ActiveWorkbook.Worksheets(i).Cells.Replace What:="[" & newWB & ".xlsm]", Replacement:="C:\YOUR PATH HERE\[ALL-IN-1 CALCULATOR 2019 with PTD.xlsm]", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Next i


End Sub

note:
replace "YOUR PATH HERE" with the file location that should look something like C:\Users\whatever\Desktop\folder or just comment it and i'll update the code
when typing the name of the new workbook just write the name, and not the file extension (i updated it) given that its an xlsm format

 

[AndyTampa]

Re: Need to save a copy of a workbook with a new name

That's not going to work at all for me.

With the original workbook 1 being closed

Workbook1 will not be closed. The whole point is to not have it close or be renamed. I only want to save a copy of Workbook1 when I need it without using the SaveAs command. It's the SaveAs command which changes the name of the workbook and breaks the formula references in the other workbook.

The copy of workbook 1 with the new name open and saved

I don't need the copy left open. I need the original left open and unchanged. I need the copy with the new name closed and saved.

and the 2nd workbook (where the macro will be located in) open

The macro will not be located in the 2nd workbook. It will be located in my PERSONAL macros workbook. When it does what I'm asking, it will be useful for more than this single workbook.

This whole process of breaking the formulas in the other workbook so they can be fixed is more complicated than just preventing the break in the first place. I don't need a macro to fix Workbook2. It's not the problem.

The problem, for which I am here seeking a solution, is how to save a copy of the currently displayed workbook in the background under a new name without affecting the currently displayed workbook or it's current name.

 

[AndyTampa]

Re: Need to save a copy of a workbook with a new name

After all this, a simple line works to create a copy without breaking references.

ActiveWorkbook.Sheets.Copy

I still have to manually save as a different name, but the references aren't pointing to it, so I get exactly what I'm asking for. It would be so much better if it would ask me for a name though and save it, which is what I really wanted, but this is 75% there.

 

[DataBlake]

Re: Need to save a copy of a workbook with a new name

It would be so much better if it would ask me for a name though and save it, which is what I really wanted,

let me know if this works for you then.

Sub referenceFIX()
  Dim newWB As String
  Dim fold As String: fold = "C:\filepath\"
  

ActiveWorkbook.Sheets.Copy
newWB = InputBox("Enter a save name")
  With ActiveWorkbook
    .SaveAs fold & newWB & Format(Date, "MM-DD-YYYY") & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
    .Close False
  End With

End Sub

change filepath to your save destination
remove "& Format(Date, "MM-DD-YYYY")" if you do not wish to include a date of your file save

 

[AndyTampa]

Re: Need to save a copy of a workbook with a new name

That works perfectly. I think I can edit the InputBox to add instructions such as "don't include the extension" or something else, but it does exactly what I wanted. Thank you.

I have a question though about the statement ".Close False". The macro closes the saved workbook, which is exactly what I want, but the statement appears to tell the macro to leave the workbook open. Is that just my lack of knowledge of VBA or is it supposed to close it?

 

[DataBlake]

Re: Need to save a copy of a workbook with a new name

The macro closes the saved workbook, which is exactly what I want, but the statement appears to tell the macro to leave the workbook open. Is that just my lack of knowledge of VBA or is it supposed to close it?

the .Close closes the document
the "false" answers the command prompt to save your current document as "no"
So if you were to click the x at the top right of excel it would ask if you wanted to save your sheet before closing. its basically clicking the don't save changes for you.
It is also setting a false value to filename change and routing the file.

 

[AndyTampa]

Re: Need to save a copy of a workbook with a new name

So I'd want the "false" because we already saved the document. Interesting. Thank you.