Save date from cell G18 into file name

Question

Sub SaveWithWEDate()
    
    Dim fileName As String
    Dim WEdate As String
    WEdate = G18
    
    fileName = "WE " & WEdate & " WEEKLY SUMMARY"
    
    ThisWorkbook.SaveAs fileName:=fileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    
End Sub

I cannot get the text from cell G18 into my file name. It saves as "WE Weekly Summary"

I also tried using the cell number in the text string.

I am using this to save a template to a macro enabled excel workbook.
I have used this before with the actual date but my sheet calculates the week ending date as the Sunday.
I copied the Sunday date into a helper cell G18 with the cell format YYYYMMDD.

Answer 1

The underlying value of your helper column is still a numerical date formatted to show "YYYYMMDD". Reading the TEXT value of the cell might return ######.

Read the value into your procedure as a date and format it within the code so it can be used in the filename.

Sub SaveWithWEDate()

Dim fileName As String
Dim WEdate As Date
WEdate = ThisWorkbook.Worksheets("Sheet1").Range("G18").Value2

fileName = "WE " & Format(WEdate, "YYYYMMDD") & " WEEKLY SUMMARY"

ThisWorkbook.SaveAs fileName:=fileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled

End Sub