Copy and Paste Error between Application instances

[tstom]

Open two intances of Excel. Rightclick some cell, choose copy. Right click another cell within any worksheet in any workbook located within the same instance, choose PasteSpecial. Now rightclick a cell anywhere within the other instance. You will see all of the available formats. My assumtion is that Range.Copy implicitly calls a hidden method "_PasteSpecial" to perform the paste in the destination range. To copy non-contiguous ranges to another application with a contigious result is not possible as far as I know. There is no comparable format to pass to the PasteSpecial method. An oversight I suppose. Anyway, assuming that you need to retain your formats, a simple workaround might be to perform an intermediary copy operation.

Sub CallTestMe()
    Dim DestWb As Workbook
With CreateObject("Excel.Application")
    .Visible = True
    Set DestWb = .Workbooks.Add
    End With
    Call TestMe(ThisWorkbook, DestWb)
End Sub
 
Sub TestMe(SrcWb As Workbook, DestWb As Workbook)
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    SrcWb.Sheets(1).Range("A1:AG2,A3329:AG3575").Copy
With SrcWb.Sheets.Add
    .Paste
    .UsedRange.Copy
    DestWb.Sheets(1).Range("A1").PasteSpecial (3)
    .Delete
    End With
End Sub

If you only need to pass values, we have some much better options.