Solved: Save file with worksheet name

[reza_doang]

HI Expert,

i have file with worksheet name like singapore.
now i hit F12/Save as, after that can be automatically save with file name "singapore". so i don't need to write for file name.

if this is possible, can you add some word like, city.
So, if i want to save as, Save As box will show up then in File Name box, it's already write "City. Singapore" then i just hit save, do not need to write for name again.

thanks

[Simon Lloyd]

You can try this in the thisworkbook module:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Fname As String
ChDir "C:\Users\YOUR NAME\Documents\"
Fname = Application.GetSaveAsFilename("City ", fileFilter:="xls Files (*.xls), *.xls")
If Fname <> "False" Then
ActiveWorkbook.SaveAs Fname
End If
End Sub

[Artik]

Try this:Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim sShName As String
Dim fileSaveName As Variant

If SaveAsUI Then

sShName = "City " & ActiveSheet.Name

ChDrive Application.DefaultFilePath
ChDir Application.DefaultFilePath

fileSaveName = Application.GetSaveAsFilename(InitialFileName:=sShName, _
FileFilter:="xls Files (*.xls), *.xls")

Cancel = True

If fileSaveName = False Then
Exit Sub
End If

Application.EnableEvents = False
Me.SaveAs Filename:=fileSaveName, FileFormat:=xlWorkbookNormal
Application.EnableEvents = True
End If
End Sub

..::Edit:
Assumption: workbook contains one worksheet.
Otherwise, may be formed in several versions of the same workbook. ::..


Artik

[reza_doang]

Simon...
Thanks for reply..
when i press F12/Save As, i get city in box file name...
what about singapore, why don't they show up?

how about like this, i have modified that my file name will be in cell XPC6,
and now when i press F12, the save as box will show up including the word in cell XPC6...

i hope you get what i want to achieve..

thanks

[reza_doang]

Artik.

thanks its work...
many thanks for you both..

[Simon Lloyd]

This should work for you:


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Fname As String
cancel=true
ChDir "C:\Users\YOUR NAME\Documents\"
Fname = Application.GetSaveAsFilename("City " & Activesheet.Range("XPC6").Value, fileFilter:="xls Files (*.xls), *.xls")
If Fname <> "False" Then
ActiveWorkbook.SaveAs Fname
End If
End Sub