TextToColumns ruining data that is already split into cells

Question

My issue:
I have a workbook, and within it, I have three different sheets. Sheet 1 is some data from a PDF-file, where I want to use my TextToColumns. Sheet 2 contains data from our financial database, whereas it is already split into cells. Sheet 3 compares the two sets of data, from Sheet 1 and Sheet 2.

The code I use to split text into columns

    Dim sht, sht2, sht3 As Worksheet
    Set sht = Sheets("PDF")
    Set sht2 = Sheets("Finance")
    Set sht3 = Sheets("Compare")

FindDate21 = sht.Range("1:1").Find("Bookkeeping Date", LookAt:=xlWhole).Address(False, False, xlA1)
DateSub = sht.Application.WorksheetFunction.Substitute(FindDate21, 1, "")
FindDateOffset = sht.Range(FindDate21).Offset(1, 0).Address(False, False, xlA1)
LastRowDateSub = sht.Cells(sht.Rows.Count, DateSub).End(xlUp).Row

rng1 = FindDateOffset & ":" & DateSub & LastRowDateSub

    sht.Range(rng1).TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
            Semicolon:=False, Comma:=False, Space:=True, 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))

My question exactly:

At the time of writing, my data in Sheet 1 is perfect, but the data in Sheet 2 is ruined. From being inserted into 16 different columns, it is shortened down into 3 columns. How can I get the TextToColumns effect, to not interfere with the data I paste in Sheet 2? Can I reverse the effect of the TextToColumns, without reversing the effect it has had on the data in Sheet 1?

Run `TextToColumns` again and set all the delimiter options to False. — Rory, Jan 02 '18 at 09:22
How does sheet 2 get populated? I don't think it should affect sheet 2 if data is already loaded there and you run the `TextToColumn` on sheet 1. — L42, Jan 02 '18 at 09:33
@L42: Sheet 2 could be affected if it is the active sheet because `Destination:=Range("A2")` is not qualified. This should be `Destination:=sht.Range("A2")` if the destination shall also be Sheet 1. But then "my data in Sheet 1 is perfect" cannot be true. So unclear question here as often. — Axel Richter, Jan 02 '18 at 09:39
@AxelRichter Yeah fair point. But shrinking 16 columns to 3 doesn't add up. Rory's initial hint is possible though when after running the TTC, you populate sheet 2. Anyways, hope OP made his question clearer :) — L42, Jan 02 '18 at 09:43
`Dim sht, sht2, sht3 As Worksheet` Not related but `sht` and `sht2` here are `Variant` type and not `Worksheet`. Anyways, have you tried any of above? What Rory and Axel mentioned? — L42, Jan 02 '18 at 09:54
What Rory has mentioned does work... But I would like to simplify it. This workbook is going to be a template, whereas I would like to do all the steps in the press of one button :) Axel's suggestion I am working on right now — Patrick S, Jan 02 '18 at 09:59
The thing is that I want to delete everything within the sheets. This delete macro also runs the code, which I have shown in the above. When I then paste the PDF data into the sheet, it is already in the right form. Then straight after I want to insert the data from our financial database, before even running the macro. Since I can't get the TextToColumns to work on just the one sheet. Actually when I was testing this at home, on my MacBook, this wasn't a problem, and now when running on a Windows PC, there is suddenly a problem with pasting after TextToColums?? — Patrick S, Jan 02 '18 at 12:12
The point was to add one more line immediately after that one to reset all the `TextToColumns` delimiters to False. That way subsequent manual pastes will not be affected. — Rory, Jan 02 '18 at 13:38
I have found a solution after all where I have taken your advice into consideration, and tried to work it out without adding more steps to my template. Thank you for your advice everyone :) — Patrick S, Jan 02 '18 at 13:41

Patrick S · Answer 1 · 2018-01-02T13:42:39.203

I have now found a solution to the problem. I am able to insert a macro on Sheet 1 and Sheet 2. Sheet 1 to apply the TextToColumns function, and Sheet 2 to reverse it again.

Macro on Sheet 1:

    Private Sub Worksheet_Activate()

Dim sht As Worksheet
Dim LastRow As Long
Set sht = Sheets("PDF Data")

LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
rng1 = "A1:A" & LastRow

sht.Range(rng1).TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1))


End Sub

Macro on Sheet 2:

Private Sub Worksheet_Activate()

Dim sht2 As Worksheet
Dim LastRow As Long
Set sht2 = Sheets("Financial Data")

LastRow = sht2.Cells(sht2.Rows.Count, "A").End(xlUp).Row
rng1 = "A1:A" & LastRow

sht2.Range(rng1).TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1))


End Sub

TextToColumns ruining data that is already split into cells

1 Answers1