0

I am new to Excel VBA and am getting stuck on how to copy and access macros between two instances of Excel. The two instances of Excel are required in order to speed up performance since they don't play well together when merged.

I have tried the following as recommended by post Copy Range between two instances of excel.

When I get to the line Set Dst = I get the error 9. I am sure that the path is right.

I've tried reversing the files xlApp, opening the files (different instances) in reverse order, verifying the extensions, and offering my computer a bribe.

Sub CopyValues()
    'Test.xlsm is the source file
    'Testd.xlsm is the destination file
    Dim xlApp As Excel.Application
    Dim Src As Range
    Dim Dst As Range
    Dim Vals() As Variant

    Set xlApp = GetObject("C:\Users\Office\Desktop\Test.xlsm").Application

    Set Src = xlApp.Workbooks("Test.xlsm").Worksheets("Sheet1").Range("A1:A9")
    Set Dst = Workbooks("Testd.xlsm").Worksheets("Sheet1").Range("A1:A9")

    Vals = Src
    Dst.Value = Vals
End Sub

I keep getting a run time error 9 run out of source file. I managed to get no errors, but also no result if run out of the destination file.

Robert Todar
  • 2,085
  • 2
  • 11
  • 31
DTinman
  • 1
  • 1
    Either `"Testd.xlsm"` doesn't exist in the active instance of Excel, or `"Sheet1"` doesn't exist in that workbook. – David Zemens Jul 07 '19 at 17:48
  • yes you're right. I just had to the order XLApp. I knew it was something simple. As a follow-up, how would I access macro on destination Testd.xlms working from Test. – DTinman Jul 07 '19 at 18:10
  • sounds like a separate question, but you should probably google that first. Hint, it most likely involves the `Application.Run` method which you'd call from `xlApp.Run`. GOod luck :) – David Zemens Jul 07 '19 at 22:25

0 Answers0