File Format

[billandrew]

Good Evening Could someone assist. I am attempting to save a current sheet (Active sheet) into a new workbook and include in the Name the reference to Range B2 which is Start & C2, which is the user input date. Also would like to include B3, which is the End & C3 which is the user input end date. A bit perplexed here....Dim FilePath$, Filename$ActiveSheet.CopyFilePath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparatorFilename = ActiveSheet.NameActiveWorkbook.SaveAs Filename:=FilePath & Filename & " " & Range("B2") & " " & Range("C2") & ".xlsx", FileFormat:=51

 

[Kenneth Hobson]

ActiveSheet.SaveAs Filename:=FilePath & Filename & " " & Range("B2") & " " & Range("C2") & ".xlsx", FileFormat:=51

 

[billandrew]

Unfortunately I'm receiving a run time error 1004

 

[Kenneth Hobson]

Check for legal filename. That means that the drive and folder must exist as well. Do a run and View the Immediate Window after to see the result.

Debug.Print FilePath & Filename & " " & Range("B2") & " " & Range("C2") & ".xlsx"

For others, record a macro to get the fileformat or see a site like: https://docs.microsoft.com/en-us/office/vba/api/excel.xlfileformat

 

[billandrew]

I believe I figured out the issue. Range is a Date which is formatted as mm/dd/yyyy. The workbook will not save with the forward slash. Need to work around this issue.Thank you Maybe someone has an alternative

 

[Kenneth Hobson]

That is a typical issue. Use Format() to format the range's date with a valid filename like "mm-dd-yyyy".

 

[billandrew]

Very good. Thank you sir.