copy data code to work with seperate instance of excel

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi this works if both workbooks are open in the same instance... however I
like to view both workbooks at once on two screens... Can this be adapted to
work in a separate instance of excel? Both workbooks will be open when code
runs

Sub copyFA()
Workbooks("FA Master").Activate
Range("v19:v20").Copy
ThisWorkbook.Activate
Range("m3:m4").PasteSpecial xlPasteValues
End Sub
 
Controlling a separate instance of excel that my code didn't start has always
been a problem for me--and I'm not sure how you'd do it if you have a few
instances of excel running.

But if you're using multiple monitors, you may want to review Chip Pearson's
notes:
http://cpearson.com/excel/MultipleMonitors.aspx

You may find that multiple instances aren't required.
 
You can try something like this. (untested)

Sub OpenAnotherInstance()

Dim OldApp As Excel.Application
Dim newAPP As Excel.Application
Dim NewWB As Excel.Workbook
Dim oldWB As Excel.Workbook
Dim NewWS As Excel.Worksheet
Dim OldWS As Excel.Worksheet

Set OldApp = Application
Set newAPP = CreateObject(, "Excel.Application")

Set oldWB = ThisWorkbook

'Opeen Workbooks FA Master in NewApp

Set NewWB = newAPP.Workbooks.Open(Filename) 'Where filename is the FA
Master workbook

'If it were me, I'd define the active sheet somehow

Set NewWS = NewWB.Worksheets("SheetName")
Set OldWS = oldWB.Worksheets("SheetName")

NewWS.Range("v19:v20").Copy
OldWS.Range("m3:m4").PasteSpecial xlPasteValues

End Sub

HTH,
Barb Reinhardt
 
Back
Top