Convert text to columns multiple sheets

[louisjunker]

Hello,

I'm trying to create a macro in VBA which should convert all cells from B2 and downwards into mutiple columns.
I got 8 sheets called D1, D2, ..., D8 - which all need to be affected by this macro.
The character to seperate will be a comma.
The macro will seperate dates from each other.

I have searched around the internet - and couldn't find anything there was working.

Can anybody help

Best regards
LJ

 

[hiker95]

louisjunker,

It would help if we can see your actual workbook/worksheets.

And, if you would manually create a new worksheet D1V2, with the results from worksheet D1 that you are looking for?


You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

Dropbox

 

[louisjunker]

I think I have uploaded it correctly.
Here:
Dropbox - Book1v2.xlsm

In the sheets D1 to D8, I need a macro to do a Text to Columns on the cells from B2 and downwards.

 

[hiker95]

louisjunker,

I have had problems in the past when attempting to download an Excel file with macros, with the xlsm file extension.

In a copy of your workbook, please remove all macros, and, then rename the copied workbook using the xlsx file extension, and, then repost on dropbox.


Then, in your next reply, also, display all of your macro code:

When posting VBA code, please use Code Tags - like this:

[code=rich]

Paste your code here.

[/code]

 

[louisjunker]

hiker95,

Ok, I have uploaded the file without macros here:
Dropbox - Book1v2.xlsx

The macros I have is at the moment these two:
Copying Macro:

 Sub sbCopyRangeToAnotherSheet()
    'Copying Raw Data from Datasheet to D1 sheet.
        Sheets("Data").Range("F2:G100").Copy Destination:=Sheets("D1").Range("A2")
    'Copying Raw Data from Datasheet to D2 sheet.
        Sheets("Data").Range("H2:I100").Copy Destination:=Sheets("D2").Range("A2")
    'Copying Raw Data from Datasheet to D3 sheet.
        Sheets("Data").Range("J2:K100").Copy Destination:=Sheets("D3").Range("A2")
    'Copying Raw Data from Datasheet to D4 sheet.
        Sheets("Data").Range("L2:M100").Copy Destination:=Sheets("D4").Range("A2")
    'Copying Raw Data from Datasheet to D5 sheet.
        Sheets("Data").Range("N2:O100").Copy Destination:=Sheets("D5").Range("A2")
    'Copying Raw Data from Datasheet to D6 sheet.
        Sheets("Data").Range("P2:Q100").Copy Destination:=Sheets("D6").Range("A2")
    'Copying Raw Data from Datasheet to D7 sheet.
        Sheets("Data").Range("R2:S100").Copy Destination:=Sheets("D7").Range("A2")
    'Copying Raw Data from Datasheet to D8 sheet.
        Sheets("Data").Range("T2:U100").Copy Destination:=Sheets("D8").Range("A2")
End Sub

And a Find and replace

Sub FindAndReplace()
Dim i As Long
For i = 1 To 8
 
    With Sheets("D" & i)
        .Range("A2:B" & .Cells(Rows.Count, "B").End(xlUp).Row).Replace What:=" ", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    End With
Next
End Sub

I now need a macro to make the data in the B columns go from text to seperet columns.
So if B2 has the text: "16.09.2017,18.06.2018"
It should make B2: "16.09.2017" and C2: "18.06.2018".

Does it make sense?

 

[hiker95]

louisjunker,

Thanks for the workbook/worksheets.


In some of the worksheets there are columns B with text, and, or strikethrus (I can not duplicate these in MrExcel):
16.09.2016,Closed

Closed,12.12.2016

,27.12.2017


strikethrus (I can not duplicate these)
05.09.2016,19.09.2016
24.10.2017,30.11.2016,21.12.2016
,27.12.2017


So that I can get it right on the first try, can I have another workbook, with all of the current worksheets, and, what the following new worksheets results should look like?

Sheet D6 --> Sheet D6V2

Sheet D7 --> Sheet D7V2

Sheet D8 --> Sheet D8V2

 

[louisjunker]

Hello,


Dropbox - Book1v3.xlsx
I have updated the sheets so D5 (V2) is how D5 should look like after the macro.
I have only done this with D5, D6, D7 and D8 - because with the current data D1 to D4 will not change.

The text "Closed" is because the date is unknown. I don't know how else to show this.
The strikethrus is only used in another excel workbook for my colleague, so she can keep track of the dates and add some comments.
It isn't relevant for my data.

Did it make sense?

 

[hiker95]

louisjunker,

Here is a macro solution for you to consider that is based on your last reply.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub ConvertTextToColumns()
' hiker95, 08/09/2017, ME1017853
Dim wary, a As Long
Dim r As Range, lr As Long, s
wary = Array("D1", "D2", "D3", "D4", "D5", "D6", "D7", "D8")
For a = LBound(wary) To UBound(wary)
  With Sheets(wary(a))
    lr = .Cells(Rows.Count, 2).End(xlUp).Row
    If lr > 1 Then
      For Each r In .Range("B2:B" & lr)
        If Not r = vbEmpty Then
          If InStr(r, ",") Then
            s = Split(r, ",")
            r.Resize(, UBound(s) + 1).Value = Application.Transpose(s)
          End If
        End If
      Next r
    End If
    .Columns.AutoFit
  End With
Next a
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ConvertTextToColumns macro.

 

[louisjunker]

hiker95,

It almost worked. It made the text to culomn, but it made the wrong text

Instead of the correct dates, it just copied the first date and inserted multiple times.

Look at this picture: Dropbox - 2017-08-11 13_02_30-Microsoft Excel - Book1v2.xlsm.png

Do you know how to fix it?

 

[hiker95]

hiker95,

It almost worked. It made the text to culomn, but it made the wrong text

Instead of the correct dates, it just copied the first date and inserted multiple times.

Look at this picture: Dropbox - 2017-08-11 13_02_30-Microsoft Excel - Book1v2.xlsm.png

Do you know how to fix it?

louisjunker,

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?


I can not work with the link in your reply #9.

The macro in my reply #8, did work correctly in your workbook/worksheets in your reply #7.


In order to continue I would have to see your actual raw data workbook, with worksheets, D1, D2, D3, D4, D5, D6, D7, D8.