FileName Manipulation Problem

hi,

I have the following code which automatically saves my worksheet into an attachment in an email and autopopulating its filename from values on my worksheet.

[vba] 'Save the new workbook/Mail it/Delete it
With ActiveSheet

TempFilePath = Environ$("temp") & "\"
TempFileName = Range("C2").Value & ", CONTRACT, " & Format(Range("C22").Value, "dd.mm.yy")[/vba]

However, the first bit of the filename is from a cell C2 which contains a name e.g. John Smith. However when saving the filename, I want this name to be reversed so would be Smith, John.

I have some code to do this from another Sub:

[VBA]Sub SaveToFile()

Dim FileName As Variant

FileName = Range("E6").Value
FileName = Mid(FileName, InStr(FileName, " ") + 1) & " " & _
Left(FileName, InStr(FileName, " ") - 1) & _
", CONTRACT, " & Format(Range("K8").Value, "dd.mm.yy")
FileName = Application.GetSaveAsFilename(FileName, "Microsoft Excel Files (*.xls), *.xls")
If FileName <> False Then

ActiveWorkbook.SaveAs FileName

End If

End Sub
[/vba]

However I tried copying and pasting into my new sub but it doenst like it because it refernces FileName twice....

any thoughts on how to implement this???

THanks alot!

works ok here but FileName is the name Excel uses as a property - it doesn't seem to matter for me but it would eliminate doubt if you use say MyFileName or TheFileName instead. If hat fails, use different variable names either side of the assignment statement (the '=' sign).

ps
Just a suggestion, if there happens to be a name such as
John Harry Smith
it currently ends up as
Harry Smith John
A small change using instrrev instead of instr may be preferred:
Mid(FileName, InStrRev(FileName, " ") + 1) & " " & Left(FileName, InStrRev(FileName, " ") - 1)
which yields
Smith John Harry

So just change every instance of FileName to MyFileName?

As a first step it would be very easy to try it.

Do i need to change this aswell?

[VBA]Dim FileName As Variant[/VBA]

That would seem to fit in with "So just change every instance of FileName to MyFileName"