1

I am getting an Application or Object Defined Error when I try to import values from one workbook to another. I have been able to resolve it by explicitly activating the workbooks and selecting the sheets before referencing the range on each, but I would like to avoid that if possible. Both workbooks are open at this point in the code. Any thoughts?

This generates errors for me:

Dim wbImport As Workbook
Dim wbReceive As Workbook
Const sExcept = "Sheet2 Name"  
Const sSht = "Sheet1 Name"
Dim rExceptions As Range

wbReceive.Sheets(sExcept).Rows(1).Insert shift:=xlDown
Set rExceptions = wbImport.Sheets(sSht).Range(Cells(rCell.Row, iHeadCol), Cells(rCell.Row, iLastCol))
wbReceive.Sheets(sExcept).Range(Cells(1, iHeadCol), Cells(1, iLastCol)).Value = rExceptions.Value 'error occurs here

This runs fine, but I'd like to avoid the .Select and .Activate

wbReceive.Sheets(sExcept).Rows(1).Insert shift:=xlDown
wbImport.Activate
wbImport.Sheets(sSht).Select
Set rExceptions = wbImport.Sheets(sSht).Range(Cells(rCell.Row, iHeadCol), Cells(rCell.Row, iLastCol))
wbReceive.Activate
wbReceive.Sheets(sExcept).Select
wbReceive.Sheets(sExcept).Range(Cells(1, iHeadCol), Cells(1, iLastCol)).Value = rExceptions.Value

As I was debugging, it looked like cells referenced in the wbReceive.Sheets(sExcept) line were actually referencing a different sheet in the wbReceive workbook. Not sure why that would be the case though, since the wb and sheet were explicitly referenced?

Community
  • 1
  • 1
nwhaught
  • 1,562
  • 1
  • 15
  • 36
  • 1
    @DavidZemens OP clearly gets that - he is just mystified on how he is doing it incorrectly in this example. – Pieter Geerkens Mar 04 '15 at 19:53
  • I acutally did review that question, and still didn't see what the issue was. Thanks @TimWilliams for a perfectly complete explanation and fix. – nwhaught Mar 04 '15 at 20:00

2 Answers2

6
Set rExceptions = wbImport.Sheets(sSht).Range(Cells(rCell.Row, iHeadCol), _
                                              Cells(rCell.Row, iLastCol))

You've qualified Range, but not Cells: either of these if not qualified with a specific sheet will refer to the ActiveSheet. Somewhat counter-intuitively, the (qualified) wrapping Range doesn't "casade" down...

Try this:

With wbImport.Sheets(sSht)
    Set rExceptions =  .Range(.Cells(rCell.Row, iHeadCol), _
                              .Cells(rCell.Row, iLastCol))
End with
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
0

The Cells reference within the range might be the hangup. I've gotten weird errors when using Range() with two cells in it. With just:

Range(Cells(1, iHeadCol), Cells(1, iLastCol))

It may be looking at ActiveSheet.Cells and if you try combining cells in different worksheets (or with an explicit reference on the range) you get errors. Try explicitly referencing the cells with:

Range(wbReceive.Sheets(sExcept)Cells(1, iHeadCol), wbReceive.Sheets(sExcept)Cells(1, iLastCol))

It looks ugly but you can probably clean it up with some worksheet objects.

Sobigen
  • 2,038
  • 15
  • 23
  • *It may be looking at ActiveSheet.Cells* It **IS** looking at the `ActiveSheet.Cells`. Just because it's inside the parentheses doesn't exempt it from the need to be qualified :) – David Zemens Mar 04 '15 at 19:54