CODEWITHSUNDEEP
vbaexcel
VolExpansion
VolExpansion

Reputation: 1

VBA Code to Copy and Paste Between Separate Excel Instances

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

Upvotes: 0

Views: 1105

Answers (1)

YowE3K
YowE3K

Reputation: 23974

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

Upvotes: 1

Related Questions