Vba to save as filename = certain characters of cells than format those characters

[baker_89]

I have some code that updates a bunch of data from another sheet now I want to add in to automatically SaveAs and have the file name automatically added from a cell. I have most of it working except the characters I am copying are formatted MM/DD/YY and the "/" cannot be a part of the file name. So I need to format it to "MM-DD-YY DDD" I've tried a few different ways but it keeps getting errors.

This is what I have for that part of the code.

Dim FileName As String
Dim fileSaveName As Variant

FileName = "Sheet" & range("A1").Characters(start:=22, Length:=9).Text (format here?)
fileSaveName = Application.GetSaveAsFilename(InitialFileName:=FileName , _
fileFilter:="Excel Files (*.xlsm), *.xlsm")
InitialFilePath = "C:\Temp\"


Comes up as "Sheet 05/16/15".

I want it to read "Sheet 05-16-15 (Sat)"

 

[sericom]

FileName = "Sheet " & Format(Range("A1").Characters(start:=22, Length:=9).Text, "mm-dd-yyyy (dd)")

 

[baker_89]

FileName = "Sheet " & Format(Range("A1").Characters(start:=22, Length:=9).Text, "mm-dd-yyyy (dd)")

Worked Perfect!

Also is there a way to get the cell values to update before the SaveAs Dialog comes up? Currently the code runs and the SaveAs dialog shows than the values do no update until after the Save is complete. I would like them to show before the user saves so they can double check things are correct.

 

[baker_89]

Worked Perfect!

Also is there a way to get the cell values to update before the SaveAs Dialog comes up? Currently the code runs and the SaveAs dialog shows than the values do no update until after the Save is complete. I would like them to show before the user saves so they can double check things are correct.

Woops! Answered my own question, just had to change to True - "Application.ScreenUpdating = False"