Can't copy selection from another workbook

Question

I've been trying to copy data from another workbook in the same instance of excel without success. What I need is to copy the selection I've made on the other opened workbook and paste it to the active workbook. Turns out that when I run the VBA code the selection copy is lost (the marching ants disappear).

I've tried the code below and variations but it never works.

Private Sub PasteCorrection()

    On Error Resume Next
    Workbooks(2).Worksheets(1).Selection.Copy ThisWorkbook.Worksheets(1).Range("C7")
    ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True
    ActiveSheet.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End Sub

Thanks in advance!

Remove `On Error Resume Next` and you will see your error messages. This line just hides all error messages, but the errors still occur, you just cannot see them (you are blind). You cannot fix the errors if you don't see them. • Read and apply [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) this will fix your errors then. — Pᴇʜ, Nov 13 '18 at 14:02
The error is related to not having content to be pasted since the copy area is no longer available in the clipboard after I run the vba code. — FerPavao, Nov 13 '18 at 15:42

GTPV · Answer 1 · 2018-11-13T16:09:22.600

1

As people have mentioned in the comments, you need to remove the On Error Resume Next because it prevents the error message from appearing.

You also cannot use Workbook.Worksheet.Selection, this plains does not exists.

So, assuming you have the macro in the destination workbook, your function could (but shouldn't!) look like that:

Private Sub PasteCorrection()

    Selection.Copy
    ThisWorkbook.Worksheets(1).Range("C7").Select
    ActiveSheet.Paste

End Sub

No it shouldn't look like that because as another commenter said, you should avoid selections as much as possible. The function below will achieve the same but much faster and without hitting the clipboard.

Private Sub PasteCorrection()

    ThisWorkbook.Worksheets(1).Range("C7").Value = Selection.Value

End Sub

Even better, if you know the range that the ranges are the same you could use the following:

Private Sub PasteCorrection()

    ThisWorkbook.Worksheets(1).Range("C7").Value = Workbooks(2).Worksheets(1).range("C7").Value

End Sub

You should of course adapt the "C7" to match your specific needs.

UPDATE

If the macro is inside the VBA of the origin workbook then the code should be different. Assuming that you know the file name of the destination workbook.

Private Sub PasteCorrection()

    Selection.Copy
    Workbooks("FileNameWithoutExtension").Worksheets(1).Paste

End Sub

If you do not know the name of the destination BUT you have only two workbooks opened:

Private Sub PasteCorrection()

    Selection.Copy
    If ThisWorkbook Is Workbooks(1) Then
        Workbooks(2).Worksheets(1).Paste
    else
        Workbooks(1).Worksheets(1).Paste
    End If

End Sub

edited Nov 13 '18 at 16:09

answered Nov 13 '18 at 14:51

GTPV

407
3
5

This one didn't work. Private Sub PasteCorrection() ThisWorkbook.Worksheets(1).Range("C7").Value = Selection.Value End Sub I do not know the range because it changes depending on the content of the other workbook. The problem is that the contents to be copied are "lost", see the image below to understand better: Content copied to clipboard: https://ibb.co/coX3ef Content after running vba code: https://ibb.co/fWXfQL – FerPavao Nov 13 '18 at 15:33
You have two problems: matching the ranges size for a proper copy/paste and making sure that the activeworkbook is not the same as the "copied-from" workbook – GTPV Nov 13 '18 at 15:42
A very plain and simple solution could be the following two lines:" Selection.Copy" followed by "ThisWorkbook.Worksheets(1).Paste" But you have to make sure that the module which contains the macro is located in the destination workbook. – GTPV Nov 13 '18 at 15:50
Copying the selection the way you suggested doesn't work because it copies from the same workbook. I need it to get the selection of the other workbook opened. – FerPavao Nov 13 '18 at 15:53
So I guess that your macro is inside the "origin" workbook? And not the destination workbook? If that is the case, then you need to know the file name of the destination workbook otherwise you can't know precisely where you are copying. I edited the answer to reflect this case. – GTPV Nov 13 '18 at 16:09

Can't copy selection from another workbook

1 Answers1