VBA: Split out Worksheets to individual Workbooks

rezacs

New Member
Joined
Sep 24, 2018
Messages
22
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.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
How about
Code:
For Each xWs In ThisWorkbook.Sheets
   Select Case xWs.Name
      Case "[COLOR=#ff0000]abc[/COLOR]", "[COLOR=#ff0000]xyz[/COLOR]", "[COLOR=#ff0000]SPQR[/COLOR]"
      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
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top