Sub ПримерИспользования()
     txt = ClipboardText
     MsgBox txt, vbInformation, "Содержимое буфера обмена Windows"
End Sub

Function ClipboardText() ' чтение из буфера обмена
    With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
         .GetFromClipboard
         ClipboardText = .GetText
     End With
End Function

Sub SetClipboardText(ByVal txt$) ' запись в буфер обмена
    With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
         .SetText txt$
         .PutInClipboard
     End With
End Sub

Application.ClipboardFormats

Sub test_ClipboardFormats()
    Dim aFmt
    For Each aFmt In Application.ClipboardFormats
        Debug.Print aFmt
    Next
End Sub

Private Declare Function GetClipboardData Lib "user32" _
    (ByVal uFormat As Long) As Long

Private Declare Function IsClipboardFormatAvailable Lib "user32" _
    (ByVal uFormat As Long) As Long

Dim rRng As Range
On Error Resume Next
Set rRng = ' а вот тут как раз и засада: как из буфера обмена что-то запихнуть в переменную чтобы выяснить, "подходит ли размер"?  
If Err Then Exit Sub

Sub tt_()
     On Error Resume Next
     Dim txt$, i&, lCount&
     Dim splTmp1$(), splTmp2$()
     txt = ClipboardText

     splTmp1 = Split(Application.Trim(txt), Chr(13) & Chr(10))
     For i = 0 To UBound(splTmp1)
         If InStr(splTmp1(i), Chr(9)) Then
             splTmp2 = Split(splTmp1(i), Chr(9))
             lCount = lCount + (UBound(splTmp2) + 1)
         End If
     Next
     If lCount = 0 Then lCount = UBound(splTmp1)

     If lCount <= 0 Then MsgBox "В буфере ничего НЕТ"
     If lCount = 1 Then MsgBox "В буфере НЕ массив"
     If lCount > 1 Then MsgBox "В буфере массив"
     Debug.Print lCount
End Sub

Function ClipboardText()
     With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
         .GetFromClipboard
         ClipboardText = .GetText
     End With
End Function

Sub NoAPIClipboard()
     Dim bEv As Boolean, bSUp As Boolean
     bEv = Application.EnableEvents
     bSUp = Application.ScreenUpdating

     Application.EnableEvents = False
     Application.ScreenUpdating = False
     Dim wkbTemp As Workbook: Set wkbTemp = Workbooks.Add
     ActiveSheet.Paste

     Dim vRng(): vRng = ActiveWindow.RangeSelection.Value
     Dim lW As Long, lH As Long
     lW = UBound(vRng, 2) - LBound(vRng, 2) + 1
     lH = UBound(vRng, 1) - LBound(vRng, 1) + 1
      
     Debug.Print lH & " x " & lW

     wkbTemp.Close False

     Application.ScreenUpdating = bSUp
     Application.EnableEvents = bEv
      
     Stop    ' см. Locals
      
     Dim rngDonor As Range
     Set rngDonor = ActiveCell.Resize(lH, lW)
      
     rngDonor.Value = vRng
End Sub