TEXT TO COLUMNS WITH VBA

h380am

New Member
Joined
Apr 25, 2020
Messages
14
Office Version
  1. 365
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

VBA Code:
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

h380am

New Member
Joined
Apr 25, 2020
Messages
14
Office Version
  1. 365
but I have this msg IN SCRRENSHOT

Can someone please assist regarding this issue ?

Best regads,
 

Attachments

  • SS VBA.JPG
    SS VBA.JPG
    20.3 KB · Views: 3

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,056
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
First of all do you have any merged cells?
Second, what happens with the code below?

VBA Code:
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
 
Last edited:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,056
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Should have tested a bit, test with the below and still need to know about the merged cells...

VBA Code:
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
 
Last edited:

h380am

New Member
Joined
Apr 25, 2020
Messages
14
Office Version
  1. 365

ADVERTISEMENT

@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 )
 

Attachments

  • SC VBA 2.JPG
    SC VBA 2.JPG
    52.6 KB · Views: 0

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,056
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
See my last post
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,056
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You're welcome. Your main problem was that you weren't qualifying your ranges with the sheets.. note the red periods/full stops below

Rich (BB code):
            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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,921
Messages
5,639,004
Members
417,062
Latest member
CM214

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