Finding the currently selected cell in another worksheet

  • Thread starter Thread starter Jeroen Moolhuijsen
  • Start date Start date
J

Jeroen Moolhuijsen

Hi,

In Excel 2002 I want to display the current row of worksheet A in worksheet
B (in another format).

Does anyone know how to do this? Ideal would be if this updates
automatically, but performance wise I guess a macro activated by a button in
worksheet A would be the best solution.

TBH, Jeroen
 
Jeroen,

I don't know of a way in a formula to determine the active cell of the
worksheet. Worksheets other than the active one don't have an active cell.
I think macros are the only way. Put this in a regular module:

Public Roww As Long

Put this in the sheet A module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Roww = ActiveCell.Row ' get current row
End Sub

Put this in the sheet B module:

Private Sub Worksheet_Activate()
' put column 5 of active cell row sheet A into this sheet A2:
Range("A2") = Sheets("A").Cells(Roww, 5)
' put column 2 of active cell row sheet A into this sheet B2:
Range("B2") = Sheets("A").Cells(Roww, 2)
End Sub

Now when you select a cell in Sheet A, it the row number will be stored in
public variable Roww. When you select Sheet B, it will populate the cells
with cells from the row of the active cell of sheet A. No buttons needed.
 
Back
Top