Lose .xlsx designation when adding variable to Save As filename

[Factotum]

Can anyone help me figure out where I'm going wrong? I'm trying to name a file: Department Name_Row Count_Sum of Amount.xlsx

It works great until I try to add the sum in there. When I add the sum, it saves as a different file format. I'm not sure what kind, except that my computer doesn't have the right program to reopen the file once I close it.

I need to add the row count and total $ amount to the filename to keep the user happy and allow them to double check the numbers at a glance. I would prefer to keep the number formatting with the commas and possibly even with a $ sign. Like I said, I can get all of this to work, but it saves it as an unknown file type.

I've even tried calculating the sum in a cell of the spreadsheet and adding: Range("BK1").value to the Save As filename.

Any suggestions on why it does not save as .xlsx when I add that last piece (& y) to the file name? Thank you for the help!

    Dim LastRowCount As Long

    'Count total rows
    With ActiveSheet
        LastRowCount = .Cells(.Rows.Count, "A").End(xlUp).Row - 1
    End With

    Dim x, y As String
    x = Application.Sum(Range("S:S"))
    y = Format(x, "#,000.00")
    
    'Save As file name
    strFilename = Range("AY2").Value & "_" & LastRowCount & "_" & y

    'Create new folder at specified location and name
    MkDir strDefpath & strDirname
    strPathname = strDefpath & strDirname & "\" & strFilename
    
'Save as .xlsx format
    ActiveWorkbook.SaveAs Filename:=strPathname, FileFormat:=51

 

[SpillerBD]

When you retrieve a filename, it does not include the extension. You'll need to add it to your text sting.

 

[Factotum]

Thanks SpillerBD - I love it when solutions are simple like that. All I did was add & ".xlsx" to the end. I had tried that once, but didn't put the "" around it. Thanks for setting me straight!

    'Save As file name
    strFilename = Range("AY2").Value & "_" & LastRowCount & "_" & y & ".xlsx"