VBA for SaveAs workbook

[GuyGadois]

I am using the following code to save a current workbook but I would like to modify it to save the current workbook as the new name and open that new workbook and close the original and leaving it untouched. Basically, using a Saveas instead. Currently it just saves the workbook as a new file and the user keeps working on the original workbook, not the saved one.

here is my code...

Sub SaveAsXLS()
     
    Dim FName           As String
    Dim FNumber         As String
    Dim fPath           As String
         
     FName = Range("Account_Name").Value
     
     fPath = Range("FilePath").Value
     
     ActiveWorkbook.SaveCopyAs fPath & FName & " - " & Format(Date, "YYYY.MM.DD") & ".xlsm"
     
     MsgBox "The File Has Been Saved as an Excel File" & vbNewLine & vbNewLine & "File Name: " & FName & vbNewLine & "Destination: " & fPath & vbNewLine


End Sub

Cheers

Guy

 

[shg]

Change SaveCopyAs to SaveAs.

 

[GuyGadois]

Change SaveCopyAs to SaveAs.

Thanks for the response but when I change it to just read SaveAs I get an error that did not occur when I use SaveCopyAs

"Run-time Error '1004':
The file could not be accessed. Try one of the following:
Make sure the specified folder exists (it does)
Make sure the folder that contains the file is not read-only (it isn't)
Make sure it doesn't use <> ? [] : or * (It DOES use [ and ]. Why is this a problem with SaveAs and not SaveCopyAs)?

 

[shg]

I just verified that that is indeed the case, and it surprises me. So pick another name without brackets.

 

[GuyGadois]

I just verified that that is indeed the case, and it surprises me. So pick another name without brackets.

Or maybe I could just replace the "[" and "]" with ""

I tried this but it doesn't work. Any ideas how to replace both [ and ] with "" in VBA code?

FName = Replace(Range("Account_Name").Value, "]", "")(Replace(Range("Account_Name").Value, "[", ""))

Guy

 

[shg]

  FName = Replace(Replace(Range("Account_Name").Value, "[", ""), "]", "")

 

[GuyGadois]

  FName = Replace(Replace(Range("Account_Name").Value, "[", ""), "]", "")

Thank you. That works perfectly!

 

[shg]

You're welcome.