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.
Edit
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:
A1=14
A2=MYRANGE
B2=MYRANGE
.
.
.
IV2=MYRANGE
MYRANGE is defined as
=INDIRECT("SourceSheet!"&DestSheet!$A$1&":"&DestSheet!$A$1)
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:
MYDESTRANGE=SourceSheet!MYSOURCERANGE
By changing MYSOURCERANGE to another row, you would then effectivly change the source of MYDESTRANGE, while using the same row as before.