Results 1 to 4 of 4

Thread: VBA: Split out Worksheets to individual Workbooks

  1. #1
    New Member
    Join Date
    Sep 2018
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA: Split out Worksheets to individual Workbooks

    I have a workbook that has 14 worksheets. I'm looking for some code that will split these worksheets out into individual workbooks while retaining formatting.

    I did find some code that does essentially what I am looking for:

    Code:
    Sub Splitbook()
    'Updateby20140612
    Dim xPath As String
    xPath = "C:\Users\user\Desktop\Test"
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each xWs In ThisWorkbook.Sheets
        xWs.Copy
        Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xlsx"
        Application.ActiveWorkbook.Close False
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    End Sub
    Unfortunately I also need to tell it to ignore 4 of the worksheets and only break 10 of them out into workbooks.
    I tried including the following If statement to no avail:

    Code:
    For Each xWs In ThisWorkbook.Sheets
        If xWs.Value <> "foo" Then
            xWs.Copy
    Any help would be appreciated.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA: Split out Worksheets to individual Workbooks

    How about
    Code:
    For Each xWs In ThisWorkbook.Sheets
       Select Case xWs.Name
          Case "abc", "xyz", "SPQR"
          Case Else
             xWs.Copy
             Application.ActiveWorkbook.SaveAs filename:=XPath & "\" & xWs.Name & ".xlsx"
             Application.ActiveWorkbook.Close False
       End Select
    Next
    Change values in red to match the sheet names you DON'T want to copy
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Sep 2018
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Split out Worksheets to individual Workbooks

    That works perfect, just what I needed.

    Problem solved.

    Thank you very much.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA: Split out Worksheets to individual Workbooks

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

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
  •