Macro to Save File under New Name with space before month

[howard]

I have the following workbook below

 BR1 Purchase_Invoices lissting Aug-2022.xlsm

I have the following code to change the month and year based on the month and year in cell D2 on sheet "Recon"


When the mcro is run , the name is changed to

 BR1 Purchase_Invoices lisstingSep-2022.xlsm

I would like it to have space before the month and year for eg

 BR1 Purchase_Invoices lissting Sep-2022.xlsm

 Sub Save_fileunderMonth()

     Dim strNameToSave As String
    Dim strFullNm   As String
    Dim lPos        As Long
    Dim s           As String

  strNameToSave = Format(ThisWorkbook.Worksheets("Recon").Range("D2").Value, "mmm-yyyy")
    strFullNm = ThisWorkbook.FullName
 
    If InStr(1, strFullNm, strNameToSave, vbTextCompare) = 0 Then
        lPos = Len(strFullNm) - Len(strNameToSave) - 4
        Mid(strFullNm, lPos, Len(strNameToSave)) = strNameToSave
        ThisWorkbook.SaveAs Left(strFullNm, Len(strFullNm) - 5), 52
        End If
End Sub

It would be appreciated if someone could kindly amend my code

 

[DanteAmor]

Try this:

Sub Save_fileunderMonth()
  Dim strNameToSave As String
  Dim strFullNm   As String
  Dim s           As String
  
  strNameToSave = Format(ThisWorkbook.Worksheets("Recon").Range("D2").Value, "mmm-yyyy")
  strFullNm = ThisWorkbook.FullName
  
  If InStr(1, strFullNm, strNameToSave, vbTextCompare) = 0 Then
    s = Left(strFullNm, Len(strFullNm) - Len(strNameToSave) - 5) & " " & strNameToSave
    ThisWorkbook.SaveAs s, 52
  End If
End Sub

 

[howard]

My apologies Dante. I thought that I had resoponded to you


Your code works perfectly