Why is Workbook.Add causing this issue?

[Mallycat]

I am getting very frustrated with this. This is a subset of some code I am working on.

Please Login or Register  to view this content.

The first line y= works fine.

the second line y= fails. Why why why? What does workbooks.add do to stuff up this line of code? Can anyone help me?


Matt

[Bryan Hessey]

I am getting very frustrated with this. This is a subset of some code I am working on.

Please Login or Register  to view this content.

The first line y= works fine.

the second line y= fails. Why why why? What does workbooks.add do to stuff up this line of code? Can anyone help me?


Matt

Matt,

they both fail for me, but would you not need to re-focus your workbook after the 'Workbooks Add' with

Workbooks(myDataBook).Activate
?


added - works as

Please Login or Register  to view this content.

---

[Mallycat]

But why can't I refer to a cell in another book without having it as the active book? I would have thought the full path workbook.sheet.range should do it.

[Bryan Hessey]

But why can't I refer to a cell in another book without having it as the active book? I would have thought the full path workbook.sheet.range should do it.

hmm, . . now it's working as originally shown, with $a$1:$a$12 in y afterwards,
providing you start in the sheet for which you have the code.

It is a 'focus' thing, but atm beyond my +/-5% knowledge.

If you trigger the code whilst active in another book it fails.

It appears to be a required focus of the command in use.

If you change to

Please Login or Register  to view this content.

then they work irrespective of focus (as you expected)

Please Login or Register  to view this content.

also works - but for a wrong result.

as stated, It appears to be a required focus of the command in use, and that requires concept thinking re workbooks, worksheets and focus for which commands.

hth
---

[Mark@Work]

I haven't done much VBA programming, but have done a fair amount in other languages.
Am I correct in thinking that you would have to add more context to the cells property? Thus:

y = Workbooks(myDataBook).Sheets(myDataSheet).Range("A1",_
Workbooks(myDataBook).Sheets(myDataSheet).Cells(myRows,_
1)).Address

Mark

[Bryan Hessey]

I haven't done much VBA programming, but have done a fair amount in other languages.
Am I correct in thinking that you would have to add more context to the cells property? Thus:

y = Workbooks(myDataBook).Sheets(myDataSheet).Range("A1",_
Workbooks(myDataBook).Sheets(myDataSheet).Cells(myRows,_
1)).Address

Mark

Yes Mark, that certainly works, the 'focus' problem being on the 'Cells' not the 'y='


That should help Matt.

---

[Mallycat]

Thanks for explaining this. I was actually helping someone else using this code and ended up taking the "got focus" approach. I will try to remember this for next time.

[Bryan Hessey]

Thanks for explaining this. I was actually helping someone else using this code and ended up taking the "got focus" approach. I will try to remember this for next time.

Good to see it work for you, it's nice and simple (so that I can understand it too)

---