PDA

View Full Version : ActiveCell - shifting from one workbook to another



krishnak
03-21-2007, 12:06 PM
Hi All,

I have two workbooks open (Book1 visible and the Book2 minimized). For all the names in a specific range of Book1, I want to find the matches for another specific range in Book2. Both the ranges are in one column each.
I selected the start cell of the range of Book1 and then applied the code
Set rangeOne = Workbooks("Book1").Range(ActiveCell, ActiveCell.End(xlDown)). Now I want to shift the ActiveCell by code to the starting cell of the Book2 (which is still minmized). The starting cell of the Book2 is Range("D2"). When I apply the code Workbooks("Book2").Range("D2").Activate, I am getting an error.
I want to make that cell as the active cell and set the rangeTwo by a similar formula.
How can I do it?
Thanks for all the help I can get.

- Krishna

JimmyTheHand
03-21-2007, 11:24 PM
Hi :hi:

You can't activate a cell if it's not part of the active sheet. You must activate Book2 and the proper sheet before activating the cell. If that's what you really want. In fact, you don't need to activate anything to set the range by the presented method. Try this
Set RangeTwo = Workbooks("Book2").Sheets("SheetName").Range("D2", Workbooks("Book2").Sheets("SheetName").Range("D2").End(xlDown))

You may want to define a Worksheet object, in order to make it easier to create cell references to that sheet. Like this
Dim Wks as Worksheet
Set Wks = Workbooks("Book2").Sheets("SheetName")

Set RangeTwo = Wks.Range("D2", Wks.Range("D2").End(xlDown))
'which is the same as
Set RangeTwo = Range(Wks.Range("D2"), Wks.Range("D2").End(xlDown))

And Wks can be reused in any other parts of the subroutine.

Jimmy

krishnak
03-22-2007, 08:34 AM
Thanks for the tip, Jimmy. It is good to know another way of setting a range. I'll try that and come back if I find any problems.

- Krishna