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

[infinity]

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)!



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.

[macropod]

That's as simple as:

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

[infinity]

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!


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

[macropod]

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:

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

[infinity]

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