VBA Code to Copy and Paste Between Separate Excel Instances

Question

I am trying to copy data from one open instance in excel and load it into a separate open instance. I have the following code but it only copies data from the source workbook since the last save. Also this code can only be run from the destination workbook. Any help would be greatly appreciated.

Sub CollectA()

Dim oApp As Application
Dim oWb As Workbook
Set oWb = GetObject("Test two.xlsm")
Set oApp = oWb.Parent
oWb.Activate
oWb.ActiveSheet.Range("A1").Select
Selection.Copy


Workbooks("Test three.xlsm").Worksheets("Sheet1").Range("B1").PasteSpecial  Paste:=xlPasteValues


End Sub

Copy/paste is going to be difficult across different instances, but why do you even need to copy/paste - you seem to only want the values, so just use Workbooks("Test three.xlsm").Worksheets("Sheet1").Range("B1").Value = oWb.ActiveSheet.Range("A1").Value. — YowE3K
– YowE3K, Commented Jan 14, 2018 at 6:22
Yes, I only need the values. Thanks for the code simplification. I'm still looking for a way to push data from the Source workbook. — VolExpansion
– VolExpansion, Commented Jan 14, 2018 at 7:45
If you want to do it the other way around, put the macro in the "Test two" workbook, and open the "Test three" workbook from there. — YowE3K
– YowE3K, Commented Jan 14, 2018 at 8:22
Thanks for the quick follow up. Unfortunately when I run the new code it does nothing. Also, for some reason after running the new code if I close the workbook 'Test Three' and try to reopen it I only see a grey Excel screen. — VolExpansion
– VolExpansion, Commented Jan 14, 2018 at 10:15
Why do you need to have the workbooks open in different instances of Excel? — YowE3K
– YowE3K, Commented Jan 14, 2018 at 18:28

YowE3K · Accepted Answer · 2018-01-14 08:24:35Z

1

Avoid copy/paste whenever possible:

Sub CollectA()

    Dim oWb As Workbook
    Set oWb = GetObject("Test two.xlsm")

    Workbooks("Test three.xlsm").Worksheets("Sheet1").Range("B1").Value = oWb.ActiveSheet.Range("A1").Value

End Sub

If you want the macro to be in "Test two":

Sub CollectA()

    Dim oWb As Workbook
    Set oWb = GetObject("Test three.xlsm")

    owb.Worksheets("Sheet1").Range("B1").Value = Workbooks("Test two.xlsm").ActiveSheet.Range("A1").Value

End Sub

edited Jan 14, 2018 at 8:24

answered Jan 14, 2018 at 6:25

YowE3K

24k7 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Code to Copy and Paste Between Separate Excel Instances

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related