Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

There are for ex. 10 rows in one worksheet. When user clicks on one row it should be presented on another worksheet. Is it possible? Any help to do it?

EDIT: To clarify: In one sheet are presented for example student exam marks on first year: John 10 8 10 7 Nick 8 9 8 9 Maria 7 8 8 7

On 2nd sheet there are student informations on the second year: John 9 9 10 8 Nick 8 8 9 7 Maria 7 6 8 8

I want to have give some kind of final certificate for student so summary information should be presented on the third sheet. I doesn't need to be on click. There could be drop down list on the third sheet.

share|improve this question

This would be a quick macro to do so:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  With Sheet2.UsedRange
    'note, this won't copy to row 1, leaving headers intact.
    Target.EntireRow.Copy (.Rows(.Rows.Count + 1).EntireRow)
  End With
End Sub

However, using it with selection change, can be quite annoying, because it will be executed every time you select another cell - so you might want to think about using some sort of backchecking or another way to execute this.

Maybe just use the code as a custom macro and assign a shortkey.


When you want to do this only with formulas, you'll have to setup each cell of your destination row with a formula. I know of no way to change a cell directly by using a formula, except of course the cell with the formula.

My suggestion then would be, to use eigther named-ranges, VLOOKUP or MATCH.

A named-range might get quite close to what you stated:

On destination-sheet:



MYRANGE is defined as


This would then give you row 14 of the destination sheet. This will also work, if A1 is on the SourceSheet , just edit it accordingly.

Just as easily you could use two named-ranges:


By changing MYSOURCERANGE to another row, you would then effectivly change the source of MYDESTRANGE, while using the same row as before.

share|improve this answer
I thought it could be done without macros, maybe with Vlookup. – Иван Бишевац Nov 12 '12 at 14:27
"when user clicks on one row" -> this is why I assumed you needed a macro. You will need to change this requirement, if you want to avoid using VBA. To be more specific, my interpretation was: user clicks on one of 10 rows, this row gets copied to another worksheet. This is only possible with VBA. – Jook Nov 12 '12 at 14:28
I offered two non-VBA solutions while still not clear about requirements (which I agree might well have been for code, though not tagged that way) and a further two solutions (including VLOOKUP) after OP's slight clarification - but none suited, and no comment why not. (A rather bulky three screenfuls, including eight images so since deleted.) Maybe OP will come across someone better at guessing? – pnuts Jan 8 '13 at 22:40

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .