Save file to a different folder, add to file name & change from .xlsm to .xlsx

Hello everyone,

Long time no post...

I have a file where I am copying a range of cells, pasting and it to a new workbook. What I want to do is save that workbook in another folder with the same name as the original file with " - Mapping File" added to the new file name, then change the new file from .xlsm to .xlsx. I am so close with what I have but it's doing something wonky (yes I said wonky)!

Code:

---

ActiveWorkbook.SaveAs "S:\Recruiting Information\Field Recruiters\Area Books\Mapping Files\" & fileName & " - Mapping File", FileFormat:=xlOpenXMLWorkbook

---

This is doing everything I want it to do except the final file name is coming out as
"Lake Michigan Region School List.xlsm - Mapping File" (which is not even an Excel file, probably because of " - Mapping File" after the extension)

and it should be "Lake Michigan Region School List - Mapping File.xlsx" and a regular Excel file. Can anyone help??? Thank you in advance.

That's as simple as:

Code:

---

With ActiveWorkbook
  .SaveAs "S:\Recruiting Information\Field Recruiters\Area Books\Mapping Files\" & Split(.Name,".xls")(0) & " - Mapping File.xlsx", FileFormat:=xlOpenXMLWorkbook
End With

---

Well that worked... sort of. It did change the extension from .xlsm to .xlsx and put the " - Mapping File" at the end but it did not rename the file with "Lake Michigan Region School List" before the " - Mapping File". It named it "Book1 - Mapping File". I am sure it has something to do with my code. Please see below... thank you for your reply. Sorry it took me so long to respond but when I went to reply yesterday I was locked out from posting new threads or replying because I changed my email address. Have a great day!

Code:

---

    Dim fileName As String
        fileName = ActiveWorkbook.Name
    ActiveSheet.Unprotect Password:=""
        Range("M6:M506,O6:O506,Q6:Q506,W6:AB506").SpecialCells(xlCellTypeVisible).Select
            ActiveSheet.Protect Password:="", DrawingObjects:=True, Contents:=True, Scenarios:=True _
                , AllowInsertingHyperlinks:=False, AllowFiltering:=True
                    ActiveSheet.EnableSelection = xlNoRestrictions
            Selection.Copy
        Workbooks.Add
            Range("A1").Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                    SkipBlanks:=False, Transpose:=False
                        Application.CutCopyMode = False
            With ActiveWorkbook
              .SaveAs "S:\Recruiting Information\Field Recruiters\Area Books\Mapping Files\" & Split(.Name, ".xls")(0) & " - Mapping File.xlsx", FileFormat:=xlOpenXMLWorkbook
            End With

---

Your previous code lacked the 'fileName' context given in your latest post. Accordingly, I assumed that was just your attempt to retrieve the existing file's name. Given that's not the case, you can use:

Code:

---

ActiveWorkbook.SaveAs "S:\Recruiting Information\Field Recruiters\Area Books\Mapping Files\" & Split(fileName, ".xls")(0) & " - Mapping File.xlsx", FileFormat:=xlOpenXMLWorkbook

---

You are nothing short of AWESOME!!! Worked perfectly. Thank you so much!