Only copy selected cells to clipboard

[most]

If a select three cells with Ctrl and then Ctrl-C and Ctrl-V into a new cell I get the result as you can below, "b d f".

But if I paste it into a text file, I get "b c d e f"!? How come?
Is it possible to convert the clipboard data "correctly" with macro?

I'm using the clipboard data for this macro, which updates the auto filter with data from the clipboard.
I need to use clipboard since I some times copy data from other sources.

Sub UpdateAutoFilterClipboard()
  Dim a As Variant
 If ActiveSheet.AutoFilterMode = False Then
   MsgBox "There is no autofilter in active sheet!"
  Else
    a = ClipToArray()
    ActiveCell.AutoFilter Field:=ActiveCell.Column, Criteria1:=a, Operator:=xlFilterValues
 End If
End Sub
Function ClipToArray() As Variant
     Dim clip As New MsForms.DataObject
     Dim lines As String
     clip.GetFromClipboard
     lines = clip.GetText
     lines = Replace(lines, vbCr, "")
     ClipToArray = Split(lines, vbLf)
End Function

 

[Jaafar Tribak]

Here is a workaround :

1- Put this code in the ThisWorkbook Module:

Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function GetClipboardSequenceNumber Lib "user32" () As Long
#Else
    Private Declare Function GetClipboardSequenceNumber Lib "user32" () As Long
#End If

Private WithEvents cmbrs As CommandBars

Public ar As Variant

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Set cmbrs = Application.CommandBars
    Call cmbrs_OnUpdate
End Sub

Private Sub cmbrs_OnUpdate()

    Static lPrevSN As Long
    Dim oCell As Range, i As Long
    
    If GetClipboardSequenceNumber <> lPrevSN Then
        If Application.CutCopyMode = xlCopy Then
            On Error Resume Next
                Erase ar
            On Error GoTo 0
            ReDim ar(Selection.Cells.Count - 1)
            For Each oCell In Selection
                ar(i) = oCell.Value
                i = i + 1
            Next oCell
        End If
    End If
    lPrevSN = GetClipboardSequenceNumber

End Sub

2- In a Standard Module:

Option Explicit

Sub UpdateAutoFilterClipboard()
    Dim a As Variant
    If ActiveSheet.AutoFilterMode = False Then
        MsgBox "There is no autofilter in active sheet!"
    Else
        a = GetArray()
        ActiveCell.AutoFilter Field:=ActiveCell.Column, Criteria1:=a, Operator:=xlFilterValues
    End If
End Sub

Function GetArray() As Variant
    GetArray = ThisWorkbook.ar
End Function

 

[most]

Nice solution. Thanks a bunch!