Solved: FileName saving not working

[tomsweddy]

Hi,

I am using the following code to name the file when saving.

'Save the new workbook/Mail it/Delete it
TempFilePath = Environ$("temp") & "\"
'TempFileName = "Your New Employee " & Sourcewb.Name & " " & Format(Now, "dd-mmm-yy")
TempFileName = Range("E6").Value & ", CONTRACT, " & Format(Range("K8").Value, "dd.mm.yy")

The one highlighted out in the middle is the original one and it works fine where as the edited one I have put in place doesn't seem to name the file. Instead I just end up with x, CONTRACT, .xls as the filename.

It should be picking up a Name value (E6) and a date value (K8)....

Any thoughts on how to resolve???

Many Thanks

[p45cal]

It seems to work ok here.. my first thoughts would be to check carefully what is in K8, and make sure that it's looking at the sheet you think it's looking at.
I don't understand where it's getting the '.xls' from if there's only a date in K8.

[tomsweddy]

.xls at the end of the file I meant! sorry....The full file name is saved like this...... x, CONTRACT, .xls (.xls is obviosuly after the filename)

So....I put active sheet in but still not working.....? how can i be sure it is looking to the correct sheet?

'Save the new workbook/Mail it/Delete it
With ActiveSheet


TempFilePath = Environ$("temp") & "\"
'TempFileName = "Your New Employee " & Sourcewb.Name & " " & Format(Now, "dd-mmm-yy")
TempFileName = Range("E6").Value & ", CONTRACT, " & Format(Range("K8").Value, "dd.mm.yy")

End With

Thanks!

[tomsweddy]

OK I solved this now......was looking at the wrong sheet.

[p45cal]

within the With .. End with, change
TempFileName = Range("E6").Value & ", CONTRACT, " & Format(Range("K8").Value, "dd.mm.yy")
to
TempFileName = .Range("E6").Value & ", CONTRACT, " & Format(.Range("K8").Value, "dd.mm.yy")

but better still, be explicit about which sheet, so that it doesn't matter which sheet is the active sheet at the time the code is executed. Eg.:
With Thisworkbok.Sheets("MySheetName")
'your code
End with