Results 1 to 4 of 4

Saving multiple worksheets into separate files of a different format

This is a discussion on Saving multiple worksheets into separate files of a different format within the Excel Questions forums, part of the Question Forums category; I have a workbook with 500+ worksheets. I would like to save each worksheet as its own file with the ...

  1. #1
    New Member
    Join Date
    May 2013
    Posts
    41

    Default Saving multiple worksheets into separate files of a different format

    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!

  2. #2
    Board Regular
    Join Date
    Mar 2010
    Location
    Bradford, West Yorkshire, UK
    Posts
    1,287

    Default Re: Saving multiple worksheets into separate files of a different format

    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

  3. #3
    MrExcel MVP Cindy Ellis's Avatar
    Join Date
    Jun 2006
    Location
    California
    Posts
    1,611

    Default Re: Saving multiple worksheets into separate files of a different format

    Another approach, which will give the .prn format and reduce the amount of code...
    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,
    Cindy

    Excel 2007 on XP at work, 2003 on Vista at home.
    If you need to post part of your worksheet, try one of these: Excel jeanie, MrExcel HTML Maker or
    Borders-Copy-Paste

  4. #4
    New Member
    Join Date
    May 2013
    Posts
    41

    Default Re: Saving multiple worksheets into separate files of a different format

    Thanks that worked great!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com