Click SAVE to save new file - not working correctly

**vettenfun** · 10-03-2011, 11:35 AM

Hi all,
What I really wanted to do is when SAVE is clicked, save to a new file with file name derived from cell A1 (not to the current workbook, which is my template). I have come up with the code below. But, once SAVE is clicked, it would save to the current workbook (not good), generate a new file with file name derived from cell A1 (Good), and also generate another file Book1 (not good). Please help.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim FName As String
Dim FPath As String

FPath = "C:\Documents and Settings\Test Folder"
FName = Sheets("Sheet1").Range("A1").Text
ThisWorkbook.SaveAs Filename:=FPath & "\" & FName

End Sub

**barryleajo** · 10-03-2011, 01:49 PM

Hi vettenfun
Try putting your code in a normal module and calling it from your SAVE button(?)
probably add the file exension too?

**vettenfun** · 10-04-2011, 11:25 AM

No luck so far.

Originally Posted by barryleajo

Hi vettenfun
Try putting your code in a normal module and calling it from your SAVE button(?)
probably add the file exension too?

**arlu1201** · 10-04-2011, 11:48 AM

Is it ok to work with a "Save" button created using VB tools in your excel file instead of using the Excel Save button/

**vettenfun** · 10-05-2011, 12:38 PM

I prefer to use the Excel "SAVE" button instead of creating another SAVE button. Could you find what's wrong in the code above? It works, but not exactly what I'm looking for. As far as saving to the template, I can set "Read-Only" to take care this problem. However, another workbook (Book1) generated is the most serious problem for me. Can we write a command to disallow Book1 to come up? Thanks

Originally Posted by arlu1201

Is it ok to work with a "Save" button created using VB tools in your excel file instead of using the Excel Save button/