Help with VBA Code

[ThiccNugg]

Hi guys,

I need a VBA code that will resave my workbook on my desktop (regardless of user) and switch to xlsx format (macros disabled), and rename the workbook with text and a date pulled from a cell value (BC2). Below is the code I have so far but it does not seem to work (error shown in image below). If someone could please fix this for me that would be great. It would also be nice if it continued to close the current workbook and then opened the newly saved one. Thanks

Sub Resave()

Dim FileName As String
Dim Path As String

Application.DisplayAlerts = False
Path = "C:\users\" & Environ("Username") & "\Desktop"
FileName = "BCA Daily All Arrivals " & ActiveWorkbook.Sheets("MASTER").Range("BC2")

ActiveWorkbook.SaveAs Path & FileName, xlOpenXMLWorkbook
Application.DisplayAlerts = True
ActiveWorkbook.Close

End Sub

 

[Zot]

You need to have the backslash after Desktop
Path = "C:\users\" & Environ("Username") & "\Desktop\"

 

[Fluff]

As BC2 is a date you may need to change the format as you cannot use / in a file name

Path = "C:\users\" & Environ("Username") & "\Desktop\"
FileName = "BCA Daily All Arrivals " & Format(ActiveWorkbook.Sheets("MASTER").Range("BC2").Value, "dd-mm-yyyy")

 

[ThiccNugg]

You need to have the backslash after Desktop
Path = "C:\users\" & Environ("Username") & "\Desktop\"

Yes I did try that but I don't think the path seems to be the problem because I have done debug and hover over path and it is correct.

 

[ThiccNugg]

As BC2 is a date you may need to change the format as you cannot use / in a file name

Path = "C:\users\" & Environ("Username") & "\Desktop\"
FileName = "BCA Daily All Arrivals " & Format(ActiveWorkbook.Sheets("MASTER").Range("BC2").Value, "dd-mm-yyyy")

Okay I tried that and it still didn't seem to work (same error).

 

[ThiccNugg]

I solved it. I had the date format in cell BC2 as DD.MM.YYYY instead of DD/MM/YYYY...

 

[Zot]

I solved it. I had the date format in cell BC2 as DD.MM.YYYY instead of DD/MM/YYYY...

As mentioned by @Fluff in Post#3, you cannot have / in filename. I thought you are aware about it.

 

[Fluff]

Glad you sorted it & thanks for the feedback.