VBA: Save Sheet as xlsx file in original folder and original filename.

[chuckp2022]

Ok, I’m new to VBA and I’m really struggling with getting a macro to do what I want it to do.

I have a spreadsheet (Office 365) with macros enabled (xlsm)

There are 4 sheets in the spreadsheet. One of them is a planned maintenance checklist. When the work/checklist is completed, I’d like to click a button to save the sheet with the original filename as xlsx in the folder where the original file is located. It should only be the sheet that has the checklist on it. I have already placed a ‘button’ on the page to start the macro. Just can’t get the macro to do what I want.

This is what I’d like.
I’d like it to suppress warning about saving it as xlsx, over writing an original file. Basically just do it, don’t ask questions!!
Save as the original filename but as xlsx.
Save to the original folder of the file.
As a bonus, remove the button that starts the macro. Not that important though.

I have a cell that gets the filename and it’s in (N1)
Here is what’s in cell N1:
=MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-6)

I’ve been trying to use that as the fname

I’ve found many possible solutions, but they don’t do everything, and I don’t know enough about VBA to correct any issues.

This one almost works but has the wrong filename and saves is as xls, not xlsx. It’s in the correct folder though. Found it on MrExcel along with others. Can't seem to find one that does what I want.

---------
Sub Test()

' Copy activesheet to the new workbook
ActiveSheet.Copy
MsgBox "This new workbook will be saved as MyWb.xls(x)"

'Save new workbook as MyWb.xls(x) into the folder where ThisWorkbook is stored
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\MyWb", xlWorkbookNormal
MsgBox "It is saved as " & ActiveWorkbook.FullName & vbLf & "Press OK to close it"

End Sub
---------

Any help would be appreciated.

 

[Fluff]

Duplicate to: Save sheets in a workbook to the saved location of the workbook with specific filename

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.