Strange Clipboard behavior After Macro Ends

[mrfishel]

After I run an Excel macro that I have created, if I go to do another task, like copy data from a program like Showcase Query to a new Excel workbook, instead of pasting the data as individual columns into individual cells, the data pastes as non-delimited data. I am assuming that this has something to do with my previous macro running, but I am not sure how to tell Excel to clear out anything related to the previous macro and act like itself. The only solution has been to completely close out of Excel. Is anyone else familiar with this kind of after effect of a macro? Thanks

 

[Legal Tender]

To my knowledge if the macro is using text to columns then any subsequent pasting after the macro has been run will inherent the settings used from the macro. A similar thing happens if a macro runs a find command and selects match entire cell contents, finds you run after the macro will still be set to those kinds of finds. You can reset the behavior back to its default settings with a few lines of extra code. Specifically for text to columns (as that seems to be the issue you are having) I add this section of code to some of my procedures to restore the default settings:

Sheets.Add
ActiveCell.FormulaR1C1 = "ABC"
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
ActiveSheet.Delete

See if that helps.

 

[mrfishel]

To my knowledge if the macro is using text to columns then any subsequent pasting after the macro has been run will inherent the settings used from the macro. A similar thing happens if a macro runs a find command and selects match entire cell contents, finds you run after the macro will still be set to those kinds of finds. You can reset the behavior back to its default settings with a few lines of extra code. Specifically for text to columns (as that seems to be the issue you are having) I add this section of code to some of my procedures to restore the default settings:

Sheets.Add
ActiveCell.FormulaR1C1 = "ABC"
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
ActiveSheet.Delete

See if that helps.

Unfortunately this did not work for me, but I appreciate your help. Here is the last section of code in my macro. Not sure if anything here might jump out at you as the culprit.

' clean up the Deposit Amount column
    Columns("E:E").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=MID(RC[-1],33,100)"
    Range("E2").Select
    
    On Error Resume Next
    Selection.AutoFill Destination:=Range("E2:E" & LastRow)
    
    Range("E2:E" & LastRow).Select
    Columns("E:E").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("D:D").Select
    Selection.Delete Shift:=xlToLeft
    
    ' sort by carrier name, then by 835 received date date
    Columns("A:J").Select
    ActiveWorkbook.Worksheets("3 Open Payment Batch Zero").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("3 Open Payment Batch Zero").Sort.SortFields.Add Key:= _
        Range("A2:A" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    ActiveWorkbook.Worksheets("3 Open Payment Batch Zero").Sort.SortFields.Add Key:= _
        Range("B2:B" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("2 Open Payment Batch").Sort
        .SetRange Range("A1:J" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Columns("A:H").EntireColumn.AutoFit
    
    End If
    
    Range("A1").Select
        
    Sheets("1 Unposted Detail Batch Match").Select
    
    Application.DisplayAlerts = True

 

[Legal Tender]

I don't see anything in that segment of code that is using text to columns. I assumed that was the culprit based on the conditions you described in the original post (as I thought I recognized that behavior from similar experiences I have had in the past).

Would it be possible to post the entire procedure?