excel file name?

N

Nigel123

Hi

I was wondering if anyone can help me?

Basically I would like excell to save a file in the name on cell A1 for
everytime I save someone on this perticular file. For example.

cell a1 says, 1001

I would like exell to save as 1001

If at a later point I change the 1001 to 1002 and click the save button I
would like it save the file as 1002 in the same folder as the other one?

Is this possible if so how???
 
J

Jacob Skaria

Launch VBE using Alt+F11. From the left treeview double click 'This
Workbook'. Paste the below code and try..


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
strFolder = "c:\Jobs\"
ActiveWorkbook.SaveAs strFolder & Trim(Range("A1")) & ".xls"
End Sub

If this post helps click Yes
 
J

Jacob Skaria

Revised...with default folder...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim strDefFolder As String
strDefFolder = "C:\"
Application.EnableEvents = False
Application.DisplayAlerts = False
If ActiveWorkbook.Path <> vbNullString Then
strDefFolder = ActiveWorkbook.Path & "\"
End If
ActiveWorkbook.SaveAs strDefFolder & Trim(Range("A1")) & ".xls"
Cancel = True
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub
 
N

Nigel123

Hi sorry im not a real technical person so can i try do this slowly

Im not sure what VBE is??? I did however press Alt+F11 and it just created a
new worksheet for me is that what im suppose to do?
 
Top