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

Can you tell me how to keep the macro from displaying an error if I hit CANCEL if I change my mind? It also leaves behind the newly copied workbook when it errors out.

 

[DataBlake]

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

Can you tell me how to keep the macro from displaying an error if I hit CANCEL if I change my mind? It also leaves behind the newly copied workbook when it errors out.

try this, i added an if statement where if the value of whats entered in the box (or cancel is pressed) is 0 it won't execute the code to copy and save.
so hopefully you won't be naming the workbook "0"

Sub referenceFIX()
  Dim newWB As String
  Dim fold As String: fold = "C:\filepath\"
  
newWB = InputBox("Enter a save name")
If newWB <> 0 Then
ActiveWorkbook.Sheets.Copy
  With ActiveWorkbook
    .SaveAs fold & newWB & Format(Date, "MM-DD-YYYY") & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
    .Close False
  End With
Else
End If
End Sub

 

[AndyTampa]

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

Tried it. I got a Run-time error '13': Type mismatch. I assumed it was the 0 in the If statement and changed it to quotes just like in formulas. It worked. I also liked my code where I had a message in the InputBox say "Enter new filename", so I combined yours with what I had found to make this:

Sub CopyWorkbook()
  Dim strAFN1 As String, strAFN2 As String, strAFN3 As String, strAFN4 As String, strResult As String
  Dim strPath As String: strPath = "Q:\Work Performed\"
  
strResult = InputBox(strAFN1 & vbCrLf & vbCrLf & strAFN2 & vbCrLf & strAFN3 & vbCrLf & strAFN4 & vbCrLf, "File copied to...", "Enter new filename")
If strResult = "" Or strResult = "Enter new filename" Then Exit Sub
ActiveWorkbook.Sheets.Copy
  With ActiveWorkbook
    .SaveAs strPath & strResult & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
    .Close False
  End With
End Sub

This seems to work for me unless you can see some unintended consequences or have a way to streamline it. As near as I can tell, this can be used on any spreadsheet I want to save. It doesn't ask me for a path in case I want to choose a different one, but that's a macro for another day. Do you have any further insights on this?

 

[AndyTampa]

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

I'm testing the savepath option with:

Sub TEST_SAVE_COPY_WITH_PATH()    Dim strAFN1 As String, strAFN2 As String, strAFN3 As String, strAFN4 As String, strResult As String
    Dim strPath As Variant
    
    strAFN1 = "Enter the file's new name."
    strAFN2 = "Do NOT include the file extension!"
    strAFN3 = "The file will be saved as a .xlsm file"


strResult = InputBox(strAFN1 & vbCrLf & vbCrLf & strAFN2 & vbCrLf & strAFN3 & vbCrLf, "File copied to...", "Enter new filename")
If strResult = "" Or strResult = "Enter new filename" Then Exit Sub
ActiveWorkbook.Sheets.Copy
    With ActiveWorkbook
    strPath = Application.GetSaveAsFilename(strResult, "Excel Files (*.xlsm), *.xlsm")
        If strPath <> False Then
            ActiveWorkbook.SaveAs strPath, FileFormat:=xlOpenXMLWorkbookMacroEnabled
            ActiveWorkbook.Close SaveChanges:=False
        Else
            ActiveWorkbook.Close SaveChanges:=False
        End If
    End With
    
End Sub

What do you think of this? I'd like to customize the SaveAs dialog box with a different title to direct the individual to pick a folder, but I can't figure that one out yet. I'm pretty sure it's in the SaveAs options, but I don't know which one or how to format it.

I've also added code to close the new workbook when it's saved or if Cancel is pressed. I'm sure this could be written better and I'd appreciate any feedback.

 

[DataBlake]

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

I'm testing the savepath option with:
I've also added code to close the new workbook when it's saved or if Cancel is pressed. I'm sure this could be written better and I'd appreciate any feedback.

if it works it works.
the only other thing i could say is where you want the application save as box opened?
you could have it open with the path that the workbook is located in (as in most cases)

I use Application.FileDialog
it allows things like

    With Application.FileDialog([B]msoFileDialogFilePicker[/B])
        .Title = "Pick a save desination"
        .Filters.Add "Excel Files", "*.xlsm*"
        .AllowMultiSelect = False
        .InitialFileName = ThisWorkbook.Path & "\"
        If Not .Show Then Exit Sub
        wfile = .SelectedItems.Item(1)
        wdiag = InStrRev(wfile, "\")
        wpath = Left(wfile, wdiag)
        wbook = Mid(wfile, wdiag + 1)
    End With

which is an altered version of what i'm using to select a file to vlookup in (this portion just allows me to pick a file from where my workbook is located)
where you would need to change whats in bold to msoFileDialogSaveAs in order to save a file as opposed to picking a file.
try this out with its many options if you want more customization

 

[AndyTampa]

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

Now we're getting into deeper waters and I'm having trouble staying afloat.

I definitely don't want to save to the same path as the original. The original is a tool I use, but the saved document will be a product of the tool. My work and my tools reside in different folders or else I'd get lost looking for things. That said, I have a coworker who might want to use this same macro but she named her folders her way. I would like to customize the save window title and maybe add the filters.

I'm guessing that what you use is similar to what I posted. If that's right, I could play with it and see how it behaves. I may have questions though, such as, what in my code would you replace with your code?

 

[DataBlake]

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

Now we're getting into deeper waters and I'm having trouble staying afloat.

the best way to learn is to swim
if your code works as intended for you, do not change it.
there will be nothing more optimal than comfort.

the filter serves no purpose unless you plan to save over a file, or pick a file with a certain extension. So for YOU the filter is pointless.
customizing the window title is as easy as adding this to this line

strPath = Application.GetSaveAsFilename(strResult, "Excel Files (*.xlsm), *.xlsm", , [B]"INSERT TITLE HERE"[/B])

 

[AndyTampa]

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

That's awesome. I feel like I'm pushing my luck here, but the more we do, the more I get creative.

Since we've already asked for a new filename, and I can put that in the title of the SaveAs dialog box, is there any option to remove the File name: and Save as Type: boxes from the dialog so that the user only chooses a folder to save to and either saves or cancels the operation? In other words, I'd like to only show the folders so the user can't write over another file accidentally unless it happens to be the same name. I think a filter would be necessary for that to only show the folders.

Have I gone too far?

 

[AndyTampa]

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

I use Application.FileDialog
it allows things like

    With Application.FileDialog([B]msoFileDialogFilePicker[/B])
        .Title = "Pick a save desination"
        .Filters.Add "Excel Files", "*.xlsm*"
        .AllowMultiSelect = False
        .InitialFileName = ThisWorkbook.Path & "\"
        If Not .Show Then Exit Sub
        wfile = .SelectedItems.Item(1)
        wdiag = InStrRev(wfile, "\")
        wpath = Left(wfile, wdiag)
        wbook = Mid(wfile, wdiag + 1)
    End With

where you would need to change whats in bold to msoFileDialogSaveAs in order to save a file as opposed to picking a file.
try this out with its many options if you want more customization

I tried it out with msoFileDialogSaveAs and it errored out at .Filters.Add "Excel Files", "*.xlsm*". I'm getting Run-time error '438': Object doesn't support this property or method. I commented this line out and the macro will continue, but wants to save the file as an .xlsx file instead. But even if I let it continue to save it as .xlsx, it doesn't save it or doesn't put it in the selected directory.

It does look similar to the behavior of the macro I got working. Would it offer the option of removing the Save As Type and File Name boxes?

 

[DataBlake]

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

Would it offer the option of removing the Save As Type and File Name boxes?

no? i don't see why you would have used your solution over my initial one on this post if you were not wanting the dialogue boxes?
to my knowledge you can't restrict the basic functionality of a save as box, just the options it allows like selecting multiple files etc.
if you were wanting someone to select a folder to save in you could use file picker in the same kind of way we are using strResult

<code>Sub foldR()
    Dim foldR As String
    Dim diaG As FileDialog
    Dim sSelect As String
    
    Set diaG = Application.FileDialog(msoFileDialogFolderPicker)
    With diaG
        .Title = "Select a Save Destionation"
        .AllowMultiSelect = False
        .InitialFileName = Application.DefaultFilePath
        If .Show <> -1 Then GoTo err
        sSelect = .SelectedItems(1)
    End With
    
err:
    foldR = sSelect
    Set diaG = Nothing
End Sub</code>

foldR would be your folder name, just note you would have to add the "/" at the end in addition with strResult to get a valid save
so it would be something like

Sub foldR()
    Dim newWB As String
    Dim foldR As String
    Dim diaG As FileDialog
    Dim sSelect As String
  
newWB = InputBox("Enter a save name")
If newWB <> "0" Then
    Set diaG = Application.FileDialog(msoFileDialogFolderPicker)
    With diaG
        .Title = "Select a Save Destionation"
        .AllowMultiSelect = False
        .InitialFileName = Application.DefaultFilePath
        If .Show <> -1 Then GoTo err
        sSelect = .SelectedItems(1)
    End With

err:
    foldR = sSelect
    Set diaG = Nothing


ActiveWorkbook.Sheets.Copy
  With ActiveWorkbook
    .SaveAs foldR & "/" & newWB & Format(Date, "MM-DD-YYYY") & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
    .Close False
  End With
Else
End If

End Sub