Consulting

Results 1 to 3 of 3

Thread: ActiveCell - shifting from one workbook to another

  1. #1

    ActiveCell - shifting from one workbook to another

    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

  2. #2
    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
    [vba]Set RangeTwo = Workbooks("Book2").Sheets("SheetName").Range("D2", Workbooks("Book2").Sheets("SheetName").Range("D2").End(xlDown))[/vba]

    You may want to define a Worksheet object, in order to make it easier to create cell references to that sheet. Like this
    [vba]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))
    [/vba]
    And Wks can be reused in any other parts of the subroutine.

    Jimmy
    -------------------------------------------------
    The more details you give, the easier it is to understand your question. Don't save the effort, tell us twice rather than not at all. The amount of info you give strongly influences the quality of answer, and also how fast you get it.

  3. #3
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •