Working across multiple workbooks/instances 1004 error

Question

I am getting a Run-time error '1004': Method 'Ranger' of object '_Worksheet' failed on the following code.

Public sh2 As Worksheet
Public sh1 As Worksheet
Public wb1 As Workbook
Public OtherWB As Object

Sub Test()

Set wb1 = ThisWorkbook
Set OtherWB = GetObject("C:\OtherWB.xlsm")
Set sh1 = wb1.Worksheets("Sheet1")
Set sh2 = OtherWB.Worksheets("Sheet1")

sh2.Range("A7").Select
sh2.Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 3)).Copy
sh1.Range("c8").PasteSpecial xlPasteAll

End Sub

Basically I want to copy from OtherWB.xlsm which is already opened in another instance of Excel and paste it to the current workbook. The 2nd instance is necessary here as the system uses multiple monitors. I can pull cell data no problem with other properties but when it comes to using "Copy", things go a bit haywire. Is Copy not a permissible property when using Excel woorkbooks as objects?

Answer 1

You cannot use the ActiveCell of a different workbook without specifying so.

Try :

With sh2
    .Range(.ActiveCell.Offset(0, 1), .ActiveCell.Offset(0, 3)).Copy
End With

EDIT:

I didn't try the above code, in case it doesn't work, try to replace :

sh2.Range("A7").Select
sh2.Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 3)).Copy

with :

sh2.Range(sh2.Range("A7").Offset(0, 1), sh2.Range("A7").Offset(0, 3)).Copy

EDIT2 :

Also note that the .Copy method has a Destination parameter, which can be in a different workbook. Read the MSDN Range.Copy Method Reference.