Text to Column with Case Statement across certain sheets with same cell

[tmcrouse]

I have 14 sheets in one workbook and I want to run a text to column macro across only certain sheets that are identical. Here is my code and I am getting an error of: application defined or object defined error. I have the code pasted in my This Workbook and not Module. I have already tried this under Module. Any suggestions. The column that is required text to column is column B and that is identical across the worksheets I am performing this macro on. The destination in each is also identical.

Sub TextToColumns()
'Text to columns across multiple worksheets
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
Select Case UCase(ws.Name)
Case "COM", "Sheet1", "Sheet11", "Sheet12", "Sheet13"
'do not process anything
Case Else
ws.Columns(B).TextToColumns destinatation:=ws.Range("b2"), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
        ), Array(14, 1)), TrailingMinusNumbers:=True
   End Select
Next ws
Application.ScreenUpdating = True
End Sub

 

[Fluff]

There are 3 things wrong here

ws.Columns([COLOR=#ff0000]B[/COLOR]).TextToColumns [COLOR=#ff0000]destinatation[/COLOR]:=ws.Range("[COLOR=#ff0000]b2[/COLOR]"), DataType:=xlDelimited, _

Columns should be ("B") or (2)
Destination is misspelt
b2 should be B1, as you are doing the entire column you cant place it starting on row 2

 

[tmcrouse]

Sub TextToColumns()
'Text to columns across multiple worksheets
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
Select Case UCase(ws.Name)
Case "COM", "Sheet1", "Sheet11", "Sheet12", "Sheet13"
'do nothing
Case Else
ws.Columns(2).TextToColumns Destination:=ws.Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
        ), Array(14, 1)), TrailingMinusNumbers:=True
   End Select
Next ws
Application.ScreenUpdating = True
End Sub

You are a genius

 

[Fluff]

Glad to help & thanks for the feedback