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?

 

[AndyTampa]

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

Now that I'm still googling for answers, it occurs to me that I should mention I can't change Workbook #1 itself, so I can't add buttons or messageboxes to the actual workbook. I'll need this to be VBA only residing in my Personal macros file.

 

[DataBlake]

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

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 .
...
It needs to do this without changing the name of Workbook #1 and breaking the cell references in Workbook #2 .

these two things do not compute for me.
you cant save something as something with a new name and then have that new named file be referenced in an old file without updating the references. why not save workbook#1 as the same name, then save as a different name for a backup?

 

[DataBlake]

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

you could have a macro that changes your workbook references to the new name and save both workbooks?
come up with a naming convention and i'll try that. i recommend sequential or date based names

 

[AndyTampa]

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

Renaming the files every time will not work for me. The files must remain the same. They are not a paired set of files whose names I can continue to rename. In fact Workbook #1 is Read-Only. I open that file every day. I created another file, Workbook #2 , to look up information for me in Workbook #1 to make my work easier. Occasionally, I want to save the information from Workbook #1 for the next day, but if I "save as" the workbook, my cell references change when I need them to remain as they were. This means tomorrow, figuratively speaking, I'll need both original files as they were AND the new file I saved to work on tomorrow.

I've experimented with some things I've found and came up with this:

Sub CopyWorkbook()

     Dim strAFN1 As String, strAFN2 As String, strAFN3 As String, strResult As String, strPath As String

     strPath = "Q:\Work Performed\"
     strAFN1 = "Enter the copied file's new name."
     strAFN2 = "Include the file extension!"
     strAFN3 = "The file will be saved in Q:\Work Performed\"

     strResult = InputBox(strAFN1 & vbCrLf & strAFN2 & vbCrLf & vbCrLf & strAFN3, "Copied Filename", "Enter Filename")

     ThisWorkbook.SaveCopyAs (strPath & strResult)

End Sub

It does what I'm asking, but Excel "cannot open the file because the file format or file extension is not valid."

Could this be because I'm doing it with a macro-enabled file? I'm saving it to a .xlsm extension just like the original. Is there some code I'm missing?

Alternatively, I looked for a way to create static workbook and worksheet references in Workbook #2 so that they wouldn't keep changing, but I've found out that isn't possible.

 

[AndyTampa]

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

After experimenting with the SaveAs and SaveCopyAs, my personal macro file is now renamed twice. I exited all Excels and found my Personal Macro file still existed, so that was a relief. I'll hold off testing until I get a response.

 

[AndyTampa]

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

these two things do not compute for me.
you cant save something as something with a new name and then have that new named file be referenced in an old file without updating the references. why not save workbook#1 as the same name, then save as a different name for a backup?

i just now figured out what you were saying. If I wasn't clear, I only need Workbook #2 to reference Workbook #1 . Workbook #2 will never need to reference the newly saved workbook. It is saved for future use and Workbook #2 doesn't need anything from it, hence I can't have it reference the saved workbook.

 

[DataBlake]

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

i just now figured out what you were saying. If I wasn't clear, I only need Workbook #2 to reference Workbook [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1 [/URL] . Workbook #2 will never need to reference the newly saved workbook.

okay so essentially you want to save workbook #1 sometimes, which does not reference anything? or does it reference workbook 2 as well?
i don't see why the formulas would change just because you saved it as a different file if it doesn't reference another workbook. Can you provide an example of a broken formula?
Either way it sounds like you have these options:

1. If you don't need to keep the actual formulas and just the values, copy/paste as values and this will make the data static.
2. if you do want to keep the actual formulas AND they reference another workbook or even itself; you can update any formulas that say
=[Workbook1] to the new name
3. If the file location changes and that is referenced you can change those as well.

keep in mind that it could also be where you're saving these files. If you have a file in c:/users/desktop/folder and then save it in c:/users/desktop/folder/2 then that could mess up references as well.

an example will paint the clearest picture for me as well as workbook names.
Only after seeing the references and the post save erros themselves could I tell though. So please give a few examples of before and after as well as any file location changes.

 

[AndyTampa]

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

You're overthinking the "why I need this". WB1.xlsm is what I need to make a copy of. WB2.xlsm has the formulas that reference WB1.xlsm. WB1.xlsm never references WB2.xlsm.

Example: Formula in WB2.xlsm is
='[WB1.xlsm]worksheet'!$F$10

When I SaveAs WB1.xlsm as NewFile.xlsm, the formulas in WB2.xlsm change to
='[NewFile.xlsm]worksheet'!$F$10

Then when I close the new file because I won't be using it until later, my formulas are all broken. Reopening a new WB1 doesn't fix them.

I only need to save a copy of WB1.xlsm to be used later. WB2.xlsm doesn't need to reference the copy ever. WB2.xlsm must continue to reference WB1.xlsm.

The two files are not a paired set. They each have their own purpose. I use the formulas to grab data I would otherwise have to grab manually by clicking back and forth between spreadsheets. It is my shortcut to save clicks. I need it to do what I'm asking so that I don't have to shut down both spreadsheets every time I need to save WB1.xlsm. If I have to save WB2 for some reason such as adding something to it and the formulas have changed, then I'd have to fix the formulas before I can save WB2. This has happened and it's a PITA when it does. I need both sheets to remain as they are and only save a copy of WB1 with a new name that I specify.

If there was a way to make the workbook references absolute like we do with $cells, then this would be less of an issue. I've searched and found that this can't be done so I need another option. This is the best I can think of.

 

[DataBlake]

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

You're overthinking the "why I need this".

When I SaveAs WB1.xlsm as NewFile.xlsm, the formulas in WB2.xlsm change to
='[NewFile.xlsm]worksheet'!$F$10

.

yeah i dont think i'm overthinking it i just needed the info you just provided.
what is the name of the original workbook 1? im going to make you a macro that changes the references back. which is just the inverse of my 2nd suggestion.