Saving multiple worksheets into separate files of a different format

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Saving multiple worksheets into separate files of a different format

  1. #1
    New Member
    Join Date
    May 2013
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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,804
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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,802
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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 2010 on Windows 7 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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Thanks that worked great!

User Tag List

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