Simple VBA Help. Split all worksheets into work books, but three. Still Learning.

GaryG9595

New Member
Joined
Jun 13, 2014
Messages
48
Hello,
The I'm using the below macro (EX 2.) and it works perfect, but I now want to exclude the sheets named below in (Ex.1)
How can I merge this exclusion into the macro in EX 2.?
I want to split every worksheet but these three listed below.
Thanks in advance,
Gary

EX 1.
If WS.Name <> "Customized" And WS.Name <> "Create Sheets" And WS.Name <> "GF ACCTS with master and test" Then

EX 2.
Sub SplitbookTXT()
'Updateby20140612
Dim xPath As String
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.Sheets
xWs.Copy
Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".txt", FileFormat:=xlText
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
VBA Code:
For Each xWs In ThisWorkbook.Sheets
    If WS.Name <> "Customized" And WS.Name <> "Create Sheets" And WS.Name <> "GF ACCTS with master and test" Then
        xWs.Copy
        Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".txt", FileFormat:=xlText
        Application.ActiveWorkbook.Close False
    End If
Next
 

GaryG9595

New Member
Joined
Jun 13, 2014
Messages
48
VBA Code:
For Each xWs In ThisWorkbook.Sheets
    If WS.Name <> "Customized" And WS.Name <> "Create Sheets" And WS.Name <> "GF ACCTS with master and test" Then
        xWs.Copy
        Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".txt", FileFormat:=xlText
        Application.ActiveWorkbook.Close False
    End If
Next
Thanks,
I'm getting a run-time error '424 Object required. Should WS.Name be xWS.Name?
Sub Test()

For Each xWs In ThisWorkbook.Sheets
If WS.Name <> "Customized" And WS.Name <> "Create Sheets" And WS.Name <> "GF ACCTS with master and test" Then
xWs.Copy
Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".txt", FileFormat:=xlText
Application.ActiveWorkbook.Close False
End If
Next

End Sub

I also tried, same error.

Sub Test()

Dim xPath As String
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.Sheets
If WS.Name <> "Customized" And WS.Name <> "Create Sheets" And WS.Name <> "GF ACCTS with master and test" Then
xWs.Copy
Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".txt", FileFormat:=xlText
Application.ActiveWorkbook.Close False
End If
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Should WS.Name be xWS.Name?
Yes, I should have caught that.

VBA Code:
For Each xWs In ThisWorkbook.Sheets
    If xWS.Name <> "Customized" And xWS.Name <> "Create Sheets" And xWS.Name <> "GF ACCTS with master and test" Then
        xWs.Copy
        Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".txt", FileFormat:=xlText
        Application.ActiveWorkbook.Close False
    End If
Next

Please use the code tags when you post code. Simply highlight the code and click one of the code tag icons in the tool bar. that say 'Quick Wrap..." when you hover the pointer over them.
 
Solution

GaryG9595

New Member
Joined
Jun 13, 2014
Messages
48
Thank you, this worked perfectly.

Sub SplitbookTXT()
Dim xPath As String
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWS In ThisWorkbook.Sheets
If xWS.Name <> "Customized" And xWS.Name <> "Create Sheets" And xWS.Name <> "GF ACCTS with master and test" Then
xWS.Copy
Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWS.Name & ".txt", FileFormat:=xlText
Application.ActiveWorkbook.Close False
End If
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,132,979
Messages
5,656,195
Members
418,289
Latest member
Kjohno

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
Top