I have created a fancy sheet1 in which a user can input data. On that
sheet1 I have added a button. If I click on it, the contents of the
spreadsheet is copied to another sheet2 where each column represents a
field from sheet 1. So each time the user click that button, the
record is saved to that sheet2. As a result I have many records.

I would like to have the ability to recreate the first sheet with the
data from sheet2. So if the users select a record, the contents of
that record will be displayed with all its data in sheet1 populated in
a fancy way. How can I do that the best way?

It is exactly the opposite of putting the data in sheet 2. Put a button on
sheet two and reverse the code

from:
sheets("Sheet2").Range("A1") = sheets("Sheet1").Range("A1")

to:
sheets("Sheet1").Range("A1") = sheets("Sheet2").Range("A1")

You may want to get the active row number on sheet2
MyRow = sheets("Sheet2").activecell.row
sheets("Sheet1").Range("A1") = sheets("Sheet2").Range("A" & MyRow)


"Ixtreme" wrote:

I have created a fancy sheet1 in which a user can input data. On that
sheet1 I have added a button. If I click on it, the contents of the
spreadsheet is copied to another sheet2 where each column represents a
field from sheet 1. So each time the user click that button, the
record is saved to that sheet2. As a result I have many records.

I would like to have the ability to recreate the first sheet with the
data from sheet2. So if the users select a record, the contents of
that record will be displayed with all its data in sheet1 populated in
a fancy way. How can I do that the best way?

Thanks but I want something like an option to select which record to
be displayed. Something like a form that ask you: which record do you
want to retrieve? Enter record number. Then if the user selects a
record, it will fetch up the details from that record from sheet2 and
displays it in the original sheet1.
Is that possible?

Data Validation list dropdown and a few VLOOKUP formulas?


Gord Dibben MS Excel MVP

On Wed, 17 Jun 2009 15:25:58 -0700 (PDT), Ixtreme
wrote:

Thanks but I want something like an option to select which record to
be displayed. Something like a form that ask you: which record do you
want to retrieve? Enter record number. Then if the user selects a
record, it will fetch up the details from that record from sheet2 and
displays it in the original sheet1.
Is that possible?

try an input box

Set MyRange = Application.InputBox("Select Record", Type:=8)
MyRow = MyRange.Row
Sheets("Sheet1").Range("A1") = Sheets("Sheet2").Range("A" & MyRow)


"Ixtreme" wrote:

Thanks but I want something like an option to select which record to
be displayed. Something like a form that ask you: which record do you
want to retrieve? Enter record number. Then if the user selects a
record, it will fetch up the details from that record from sheet2 and
displays it in the original sheet1.
Is that possible?

Thanks, both were usefull. Don't understand why this can be soooo
easy...