0

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.

CODE USED:

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

| |
  • Try: wbNew.SaveAs Filename:=strFilename, FileFormat:=xlOpenXMLWorkbook – cybernetic.nomad Jan 16 at 18:14
  • Do you mean to add this in place of the current wbNew.SaveAs strFilename? – Jesse H. Jan 16 at 18:39
  • yes, that is what I mean – cybernetic.nomad Jan 16 at 18:41
  • @cybernetic.nomad, no luck there. The result is the same. – Jesse H. Jan 16 at 22:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.