Saving multiple worksheets into separate files of a different format

[Brandon21]

I have a workbook with 500+ worksheets. I would like to save each worksheet as its own file with the worksheet name as its file name. I found this link which does exactly that but I was wondering if I could add one more step that would save each worksheet as a (formatted text (space delimited)) file, this is a .prn file.


· Copying/Saving Worksheets from a large workbook into separate files

Code:

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




Thanks for any help!

 

[Dryver14]

Still on with this Brandon?

Try on a sheet

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, FileFormat:=xlCSV
wbNew.Close
Next ws
End Sub

 

[Cindy Ellis]

Another approach, which will give the .prn format and reduce the amount of code...

Sub SaveSheetsAsPrn()
Dim wbThis As Workbook
Dim ws As Worksheet
Dim strFilename As String


Set wbThis = ActiveWorkbook
For Each ws In wbThis.Worksheets
    strFilename = wbThis.Path & "/" & ws.Name
    ws.SaveAs strFilename, FileFormat:=xlTextPrinter, CreateBackup:=False
    
Next ws

End Sub

Hope that helps,

 

[Brandon21]

Thanks that worked great!