how to retain the clipboard data after changing workbooks in vba?

Question

i have a program that copies a range of cells and the needs to paste the contents into a new workbook that is created in code. i can copy the data but somehow the clipboard loses its data whenever i change workbooks to the new one created. i considered copying the cells to an array and then just copying the array to the new workbook but i wont know the size of the array at coding time this varies almost every time the macro runs. how do i then keep the data on the clipboard while i change the active workbook?

cell = "k7: l" & row
Worksheets(1).Range(cell).Select


Selection.Copy
relpath = ThisWorkbook.Path & "\" & "DispersionList.xls"

If Dir(relpath) <> "" Then
   Application.Workbooks.Open (relpath)
   Workbooks("DispersionList.xls").Activate
Else
    Call createWorkbook
End If

Worksheets(1).Cells(7, 14).Select
Selection.PasteSpecial


End Sub

if i run through the code line by line and check the clipboard it loses its contents at the workbooks.open line

Answer 1

There are a few actions in Excel/VBA that will void the selection/clipboard, e.g. changing any window/display settings. Thus, I suspect there is some event being called when you change the worksheet/workbook.

You can either debug it and while stepping through the code figure out, when the selection is voided and avoid this statement (if possible).

Alternatively, use subStoreClipboard and subRestoreClipboard from below code in your event code. To use the code, insert it in a new module in your worksheet - and also insert a new (hidden) worksheet which is named "ws_Temp" in VBA.

Private mIntCutCopyMode As XlCutCopyMode
Private mRngClipboard As Range

Public Sub subStoreClipboard()
    On Error GoTo ErrorHandler
    Dim wsActiveSource As Worksheet, wsActiveTarget As Worksheet
    Dim strClipboardRange As String

    mIntCutCopyMode = Application.CutCopyMode

    If Not fctBlnIsExcelClipboard Then Exit Sub


    Application.EnableEvents = False

    'Paste data as link
    Set wsActiveTarget = ActiveSheet
    Set wsActiveSource = ThisWorkbook.ActiveSheet

    With ws_Temp
        .Visible = xlSheetVisible
        .Activate
        .Cells(3, 1).Select
        On Error Resume Next
        .Paste Link:=True
        If Err.Number Then
            Err.Clear
            GoTo Finalize
        End If
        On Error GoTo ErrorHandler
    End With

    'Extract link from pasted formula and clear range
    With Selection
        strClipboardRange = Mid(.Cells(1, 1).Formula, 2)
        If .Rows.Count > 1 Or .Columns.Count > 1 Then
            strClipboardRange = strClipboardRange & ":" & _
                Mid(.Cells(.Rows.Count, .Columns.Count).Formula, 2)
        End If
        Set mRngClipboard = Range(strClipboardRange)
        .Clear
     End With

Finalize:
    wsActiveSource.Activate
    wsActiveTarget.Parent.Activate
    wsActiveTarget.Activate

    ws_Temp.Visible = xlSheetVeryHidden
    Application.EnableEvents = True

    Exit Sub
ErrorHandler:
    Err.Clear
    Resume Finalize
End Sub


Public Sub subRestoreClipboard()
    Select Case mIntCutCopyMode
        Case 0:
        Case xlCopy: mRngClipboard.Copy
        Case xlCut:  mRngClipboard.Cut
    End Select

End Sub

Private Function fctBlnIsExcelClipboard() As Boolean
    Dim var As Variant
    fctBlnIsExcelClipboard = False
    'check if clipboard is in use
    If mIntCutCopyMode = 0 Then Exit Function
    'check if Excel data is in clipboard
    For Each var In Application.ClipboardFormats
        If var = xlClipboardFormatCSV Then
            fctBlnIsExcelClipboard = True
            Exit For
        End If
    Next var
End Function