Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Save worksheets as a separate Workbook (Excel)

    I have a workbook with 5 sheets in it. I need to save each worksheet as a separate workbook. Could someone help me out with the code... something like

    For i = 1 to 5
    oSheet.Cells.Copy
    Application.CutCopyMode = False
    open new workbook
    paste
    activeworkbook.saveas "C:mainbook" & i & ".xls"
    Next i

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save worksheets as a separate Workbook (Excel)

    The code below might get you started. It will save the worksheets as separate files with filenames the same as the sheet name. It does nothing to handle the situation where a file of that name already exists.

    <pre>Public Sub SaveSheets()
    Dim oSheet As Worksheet, oNewWB As Workbook, oCurWB As Workbook
    Dim iSheetsInNewWorkbook As Integer
    Set oCurWB = ActiveWorkbook
    iSheetsInNewWorkbook = Application.SheetsInNewWorkbook
    Application.SheetsInNewWorkbook = 1
    For Each oSheet In oCurWB.Worksheets
    Set oNewWB = Workbooks.Add
    oSheet.Cells.Copy
    oNewWB.Worksheets(1).Paste Destination:=oNewWB.Worksheets(1).Range("A1")
    oNewWB.Worksheets(1).Name = oSheet.Name
    oNewWB.SaveAs Filename:="C:Work" & oSheet.Name & ".xls"
    oNewWB.Close
    Next oSheet
    Application.SheetsInNewWorkbook = iSheetsInNewWorkbook
    End Sub
    </pre>

    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Save worksheets as a separate Workbook (Excel)

    Thank you

Posting Permissions

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