Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Joliette, Quebec, Canada
    Posts
    290
    Thanks
    9
    Thanked 0 Times in 0 Posts

    converting worksheets to workbooks (2003)

    I often use the show pages tool within Excel's pivot table tool bar. It generates a separate work sheet the each of the 22 sales reps. I would like to have each work sheet converted to a separate excel file so that I could email that their XLS file to each rep.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: converting worksheets to workbooks (2003)

    The following macro will save each worksheet in the active workbook to a separate workbook with the same name as the worksheet.
    <code>
    Sub SplitWorkbook()
    Dim wbkCur As Workbook
    Dim wbkNew As Workbook
    Dim wsh As Worksheet
    Application.ScreenUpdating = False
    Set wbkCur = ActiveWorkbook
    For Each wsh In wbkCur.Worksheets
    wsh.Copy
    Set wbkNew = ActiveWorkbook
    wbkNew.SaveAs wsh.Name
    wbkNew.Close
    Next wsh
    Application.ScreenUpdating = True
    End Sub
    </code>
    You could add code to exclude specific sheets from being saved to a workbook.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: converting worksheets to workbooks (2003)

    Richard

    Try this:

    <pre>Sub CreateWorkbooks()

    Dim wb As Workbook
    Dim wbSource As Workbook
    Dim wksheet As Object
    Dim SaveToDrive As String

    Application.ScreenUpdating = False

    SaveToDrive = "C:Temp"

    Set wbSource = ActiveWorkbook

    For Each wksheet In wbSource.Sheets
    wksheet.Copy
    Set wb = ActiveWorkbook
    wb.SaveAs SaveToDrive & wksheet.Name
    wb.Close
    Next

    Application.ScreenUpdating = True

    End Sub

    </pre>

    Jerry

  4. #4
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Joliette, Quebec, Canada
    Posts
    290
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: converting worksheets to workbooks (2003)

    thanks a billion

Posting Permissions

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