Pasting/Text to Columns Inconsistencies

[Nogan]

Hi All,

First but definitely not my last contribution here! I am running into an issue with my macro where inconsistencies are appearing between various end-users. The issue falls in the pasting method; my macro starts off by pasting data from the clipboard into a new workbook and does some subsequent formatting from there. Various users are receiving error messages and incorrectly formatted data after running the macro from what I believe is due to various text-to-columns settings. I understand Excel tends to remember the previous text-to-column run when you paste data and implements these settings; would anyone be able to confirm if that is completely true?

To combat the issue, I added a new procedure that runs a mock text-to-columns with the Tab = True, which appears to sucessfully paste the data in the format that I see on my computer. Yet, this does not appear to be working for other users. Is there something I am overlooking? Does pasting data not take the settings from text-to-columns as I have assumed?

I appreciate any assistance.

See code below:

Sub ClearTextToColumns()
    On Error Resume Next
    If IsEmpty(Range("A1")) Then Range("A1") = "XYZZY"
    Range("A1").TextToColumns Destination:=Range("A1"), _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=True, _
        Semicolon:=False, _
        Comma:=False, _
        Space:=False, _
        Other:=False, _
        OtherChar:=""
    If Range("A1") = "XYZZY" Then Range("A1") = ""
    If Err.Number <> 0 Then MsgBox Err.Description
End Sub

Sub Reformatting()
'
' Reformatting Macro
'
' Keyboard Shortcut: Ctrl+Shift+X
'
' Do not copy Total Columns


Dim answer As Integer


answer = MsgBox("Did you copy the Total row?", vbYesNo + vbQuestion)


If answer = vbYes Then
    MsgBox "Please do not copy Total row, this macro will fail if you do"
    Exit Sub
Else


Application.ScreenUpdating = False


    Dim wb As Workbook
    Set wb = Workbooks.Add


' Reset text to columns


    Call ClearTextToColumns
    
' Start Macro

 

[Joe4]

Welcome to the Board!

Exactly what kind of inconsistencies are you seeing?
I notice that your Text to Columns code does not include any FieldInfo arguments, which would let Excel do as it pleases there.
See: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-texttocolumns-method-excel

 

[Nogan]

Hi Joe! Thanks for the quick response.

Inconsistencies include; various pasting outcomes. When I (on my machine) paste-special-value data from this original source to excel my output is already broken out into columns and is fairly clean (what the macro requires to work), while others receive data clumped into one column or broken out oddly. I basically need the first step of the macro, shown below, to be consistent across machines... hence why I am attempting a false text-to-columns at the beginning to ensure the settings are Tab delimited before moving on to the paste command. Obviously, that's not working in this case.

Let me know if that answers your question.

    Range("A1").Select
    ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False

 

[Joe4]

I understand Excel tends to remember the previous text-to-column run when you paste data and implements these settings; would anyone be able to confirm if that is completely true?

Yes, I have found that to be true when copying/pasting text data from other sources. I am guessing that is where the problem lies. I too wish that Excel would not do that.
So I understand that you are trying to "set" (or "reset") the Text to Columns functionality so it doesn't use the previous methodology on the data.

It looks like you got the code from this link here: http://spreadsheetpage.com/index.php/tip/clearing_the_text_to_columns_parameters/
Note that in that link, to reset them, he set them ALL to false. You have the Tab setting to True.
You might want to try totally resetting it first, like he shows in the link, before proceeding with trying to change the settings.

More importantly, how are you ensuring that this macro is being run BEFORE they try pasting the data?

 

[Nogan]

Ah I see, I will attempt setting all to FALSE first and then adding Tab to True if needed. I did receive the code from there (no need to reinvent the wheel haha).

More importantly, how are you ensuring that this macro is being run BEFORE they try pasting the data?​

That, I am monitoring via steps in the workbook containing the macro, and also standing over their shoulder ​