VBA save duplicate on close question

[piersm2]

I am having an issue with my save copy on close VBA code. I am wanting to save a OneDrive shared file (which is a .xlsb file type) upon closing the file to a network drive, but am wanting to save it as a .xlsx file, not .xlsb file type. If I use this code, the file will save, but I get an error when I try to open the duplicate file saying that it is the wrong file type. If I change it to save as a .xlsb file type, it works. I am wanting to save a regular excel workbook, not a macro workbook. Can anyone assist me?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With ThisWorkbook
Application.DisplayAlerts = False
.SaveCopyAs Filename:="J:\folder1\folder2\afilename.xlsb"
Application.DisplayAlerts = True
End With
End Sub

 

[Joe4]

Welcome to the Board!

Is this VBA code in the workbook you are trying to save? If so, then you cannot save it as an "xlsx" file, as that extension denotes that it is a Macro-free workbook.
All files with macros should be saved with "xlsm" or "xlsb".

 

[piersm2]

Is there a way to save it as a Macro-free workbook? I want the macro in the working document, but not the duplicate that is saved.

 

[Joe4]

You can have your VBA copy the data over to a new, blank workbook, and save that.
You can get a lot of the code you need for that using the Macro Recorder. If you need help cleaning up that code and adding to it, post it here and let us know.