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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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