TEXT TO COLUMNS WITH VBA

[h380am]

good day to all

is it possible to convert text to columns in differents sheet at the same time ?

I've tried to run below macro

Sub Macro1()
For ws = 1 To ThisWorkbook.Sheets.Count
        If (ThisWorkbook.Sheets(ws).Name = "EUROMAR-I" Or _
            ThisWorkbook.Sheets(ws).Name = "EUROMAR-II" Or _
            ThisWorkbook.Sheets(ws).Name = "EUROMAR-III" Or _
            ThisWorkbook.Sheets(ws).Name = "MOSHT-I" Or _
            ThisWorkbook.Sheets(ws).Name = "MOSHT-II" Or _
            ThisWorkbook.Sheets(ws).Name = "MOSHT-III") Then
            Application.ScreenUpdating = False
Dim rg As Range
With Worksheets(ThisWorkbook.Sheets(ws).Name)
Set rg = Range("A1").CurrentRegion
rg.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, Comma:=True, Space:=False, Other:=False
End With
Application.ScreenUpdating = True
        End If
    Next ws
            
End Sub

 

[h380am]

but I have this msg IN SCRRENSHOT

Can someone please assist regarding this issue ?

Best regads,

 

[MARK858]

First of all do you have any merged cells?
Second, what happens with the code below?

Sub Macro1()
    Dim ws As Long, rg As Range
    Application.ScreenUpdating = False

    For ws = 1 To ThisWorkbook.Sheets.Count
        If (ThisWorkbook.Sheets(ws).Name = "EUROMAR-I" Or _
            ThisWorkbook.Sheets(ws).Name = "EUROMAR-II" Or _
            ThisWorkbook.Sheets(ws).Name = "EUROMAR-III" Or _
            ThisWorkbook.Sheets(ws).Name = "MOSHT-I" Or _
            ThisWorkbook.Sheets(ws).Name = "MOSHT-II" Or _
            ThisWorkbook.Sheets(ws).Name = "MOSHT-III") Then
         
            With ws
                Set rg = .Range("A1").CurrentRegion
                rg.TextToColumns Destination:=.Range("A1"), DataType:=xlDelimited, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, Comma:=True, Space:=False, Other:=False
            End With
       
          End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[MARK858]

Should have tested a bit, test with the below and still need to know about the merged cells...

Sub Macro1()
    Dim ws As Long, rg As Range
    For ws = 1 To ThisWorkbook.Sheets.Count
        If (ThisWorkbook.Sheets(ws).Name = "EUROMAR-I" Or _
            ThisWorkbook.Sheets(ws).Name = "EUROMAR-II" Or _
            ThisWorkbook.Sheets(ws).Name = "EUROMAR-III" Or _
            ThisWorkbook.Sheets(ws).Name = "MOSHT-I" Or _
            ThisWorkbook.Sheets(ws).Name = "MOSHT-II" Or _
            ThisWorkbook.Sheets(ws).Name = "MOSHT-III") Then
            Application.ScreenUpdating = False

            With ThisWorkbook.Sheets(ws)
                Set rg = .Columns(1)
                rg.TextToColumns Destination:=.Range("A1"), DataType:=xlDelimited, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, Comma:=True, Space:=False, Other:=False
            End With
            Application.ScreenUpdating = True
        End If
    Next ws
        
End Sub

 

[h380am]

@MARK858
first of of all thank you for your reply

1- I don't have any merged cells
2- Compile error : type mismatch ( see attached screenshot )

 

[MARK858]

See my last post

 

[h380am]

Dear @MARK858

it's ok now
Thanks a lot for your kind assistance

 

[MARK858]

You're welcome. Your main problem was that you weren't qualifying your ranges with the sheets.. note the red periods/full stops below

            With ThisWorkbook.Sheets(ws)
                Set rg = .Columns(1)
                rg.TextToColumns Destination:=.Range("A1"), DataType:=xlDelimited, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, Comma:=True, Space:=False, Other:=False
            End With