Solved: Saving a Workbook

[chem101]

Hello Everyone,

I need some help saving a workbook. I have a workbook called 'Template' that users use to create other workbooks. I would like the user to enter data in 'Template' and then save the 'Template' workbook with the data they've entered under a different file name for future reference. I would like the user at this point to continue to work with the 'Template' workbook to create other workbooks and save those workbooks as described above. With the code I have in place this doesn't happen. Can you help me? Here's what I have so far:

[VBA]Sub GetUserSaveFile()
Dim UserFile As Variant
UserFile = Application. _
GetSaveAsFilename _
(FileFilter:="Excel Workbooks(*.xlsm), *.xlsm)", Title:="Save This Estimate As")

ThisWorkbook.Close

Workbooks.Open Filename:="Template.xlsm", _
AddToMru:=True

End Sub[/VBA]

Thank you in advance for any help you can provide!

[Bob Phillips]

[vba]

Sub GetUserSaveFile()
Dim UserFile As Variant
UserFile = Application.GetSaveAsFilename _
(FileFilter:="Excel Workbooks(*.xlsm), *.xlsm)", Title:="Save This Estimate As")

ThisWorkbook.SaveCopyAs UserFile
End Sub
[/vba]

[chem101]

Works great! Thank you very much!!

[chem101]

One question - How do we change/add to your code if: The user starts with 'Template' and modifies it, then saves it as TemplateSave1'. While 'Template' is open the user opens TemplateSave1, modifies it and wants to re-save it as TemplateSave1' and also close 'TemplateSave1' so they can continue to use 'Template'? Please advise.

Thank you!!

[Bob Phillips]

I don't understand what you are saying, that codes doesn't keep the new workbook open.

[mdmackillop]

Use SaveCopyAs to save copies and continue with the original book open

[chem101]

Here is a copy of the code:

[vba]Sub GetUserSaveFile()
Dim UserFile As Variant
UserFile = Application.GetSaveAsFilename _
(FileFilter:="Excel Workbooks(*.xlsm), *.xlsm)", Title:="Save Workbook As")
ThisWorkbook.SaveCopyAs UserFile
End Sub[/vba]

Thank you!!

[Bob Phillips]

Use SaveCopyAs to save copies and continue with the original book open

That is what he is (should be) doing MD, I suggested that earlier.

[Bob Phillips]

Here is a copy of the code:

[vba]Sub GetUserSaveFile()
Dim UserFile As Variant
UserFile = Application.GetSaveAsFilename _
(FileFilter:="Excel Workbooks(*.xlsm), *.xlsm)", Title:="Save Workbook As")
ThisWorkbook.SaveCopyAs UserFile
End Sub[/vba]

Thank you!!

So explain again, what is the problem?

[chem101]

Thank you for your resolution. I just have one more question about this item If the user starts with 'Template' and modifies it then saves it as 'TemplateSave1'. Using your code, the file is saved and the user is returned to 'Template' (perfect so far!). While in 'Template' if the user opens 'TemplateSave1 and modifies it further then tries to re-save it as 'TemplateSave1' using the code you provided we get an error that states "The workbook you are trying to save has the same name as a currently open workbook". How can the code be modified to allow the file to be re-saved under the same name? Normally Excel would ask if we want to replace the existing file. Can this be done with VBA code?
Thank you very much for your assistance!

[Bob Phillips]

Don't use that code to save 'TemplateSave1', use the Save button.

[chem101]

Not possible. For this worksheet I've disabled the ribbon. The user is saving their work via the code attached to a control button placed on the worksheet.

[Bob Phillips]

See if this does what you want

[vba]

Sub GetUserSaveFile()
Dim wb As Workbook
Dim UserFile As Variant
Dim Filename As String
UserFile = Application.GetSaveAsFilename _
(FileFilter:="Excel Workbooks(*.xlsm), *.xlsm)", Title:="Save Workbook As")
Filename = Right$(UserFile, Len(UserFile) - InStrRev(UserFile, "\"))
On Error Resume Next
Set wb = Workbooks(Filename)
On Error GoTo 0
If wb Is Nothing Then

ThisWorkbook.SaveCopyAs UserFile
Else

wb.Save
End If
End Sub
[/vba]

[Bob Phillips]

Not possible. For this worksheet I've disabled the ribbon. The user is saving their work via the code attached to a control button placed on the worksheet.

BTW, wouldn't Ctrl-S still work?