I want to acknowledge first before I ask this question that this question has been answered before here but I still cannot get it to work with my Mac. I see others had success in the thread mentioned but I have had none.
I have the developer tag turned on in Excel, I open the Visual Basic Editor, I create a module for 'ThisWorkbook', paste the code below, and hit the play button.
This definitely runs the code because I end up with each sheet as a new file in the relevant folder, but each file is a TEXTEDIT file that has a "-locked" in the file name. Upon opening, the file appears empty.
Sub CreateNewWBS() Dim wbThis As Workbook Dim wbNew As Workbook Dim ws As Worksheet Dim strFilename As String Set wbThis = ThisWorkbook For Each ws In wbThis.Worksheets strFilename = wbThis.Path & "/" & ws.Name ws.Copy Set wbNew = ActiveWorkbook wbNew.SaveAs strFilename wbNew.Close Next ws End Sub
Files are being saved as seen in the following image: saving as TEXTEDIT files with -locked in name once opened