0

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?

Josh
  • 265
  • 6
  • 11
  • 20

1 Answers1

0

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.

Bernard Saucier
  • 2,240
  • 1
  • 19
  • 28
  • sh2.Range(sh2.Range("A7").Offset(0, 1), sh2.Range("A7").Offset(0, 3)).Copy <---- We're getting closer. This copied, but when I went to paste, it pasted it as a picture object onto the other workbook. – Josh Jan 28 '14 at 18:16
  • Try : sh2.Range(sh2.Range("A7").Offset(0, 1), sh2.Range("A7").Offset(0, 3)).Copy Destination:=sh1.Range("c8") – Bernard Saucier Jan 28 '14 at 18:40
  • Copy method of range class failed. – Josh Jan 28 '14 at 19:20
  • Now that I look at it, it makes sense; the copied range is not the same size as the destination range. Try : sh2.Range(sh2.Range("A7").Offset(0, 1), sh2.Range("A7").Offset(0, 3)).Copy Destination:=sh1.Range(sh1.Range("C8"), sh1.Range("C10")) – Bernard Saucier Jan 28 '14 at 20:02
  • Am headed home in not too long, will be able to check/test from there. – Bernard Saucier Jan 28 '14 at 20:39
  • sh1.Range("C8").PasteSpecial xlClipboardFormatDspText, False '''' I think I found her!! Thanks for all your help. – Josh Jan 28 '14 at 21:26