Moving from 1 row sheet1 to sheet2
 

I have an Excel problem and I was wandering if anyone could give me
their 2 Cents worth.

Let me explain. I will have 2 worksheets Lets say A and B. In
Worksheet A I have rows and rows of data. In worksheet B I want to
create a form (not excels form one written in the spreadsheet itself no
VB) that will contain some of the info from each row. Like for
example....


Sheet A : Row 1 Name, Phone, Address
Row 2 Name, Phone, Address

Sheet B: From highlighted row 1 Name Address


What I would like is each time you click a row the info changes
in sheet B. I know this is done in Access very easily with a table and
a form but can it be done in excel


--
tomc112
------------------------------------------------------------------------
tomc112's Profile: http://www.excelforum.com/member.php...o&userid=16816
View this thread: http://www.excelforum.com/showthread...hreadid=320131

Tom,

You can use the worksheet's selection change event: copy the code below,
right click on the sheet tab, select "view code", and then paste the code in
the window that appears. This sample code will place the values from the
selected row on Sheet1 to Row 1 of Sheet2. The easiest way to sue the
information then is to link to the cell of row 1 on Sheet2, to layout the
form the way that you want. You could hide row 1 to make things look nicer.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Rows.Count 1 Then Exit Sub
Application.EnableEvents = False
Target.EntireRow.Copy Worksheets("Sheet2").Rows(1)
Application.EnableEvents = True
End Sub



"tomc112" wrote in message
...

I have an Excel problem and I was wandering if anyone could give me
their 2 Cents worth.

Let me explain. I will have 2 worksheets Lets say A and B. In
Worksheet A I have rows and rows of data. In worksheet B I want to
create a form (not excels form one written in the spreadsheet itself no
VB) that will contain some of the info from each row. Like for
example....


Sheet A : Row 1 Name, Phone, Address
Row 2 Name, Phone, Address

Sheet B: From highlighted row 1 Name Address


What I would like is each time you click a row the info changes
in sheet B. I know this is done in Access very easily with a table and
a form but can it be done in excel


--
tomc112
------------------------------------------------------------------------
tomc112's Profile:
http://www.excelforum.com/member.php...o&userid=16816
View this thread: http://www.excelforum.com/showthread...hreadid=320131

I have 4 sheets is there a way to make the code choose a sheet? I tried
if then else statements but I'm not a very good programmer and I
probably did something wrong.

I figured it would look something like this.

If Target.Rows.Count 1 Then Exit Sub
Application.EnableEvents = False
If column A (In selected Row) = Inventory then
Target.EntireRow.Copy Worksheets("Inventory").Rows(1)
If column A = RawMat1 then
Target.EntireRow.Copy Worksheets("RawMat1").Rows(1)
Application.EnableEvents = True

The problem is I think if you have multiple Inventory the code gets
confused. I only want the row that was selected not multiples.


--
tomc112
------------------------------------------------------------------------
tomc112's Profile: http://www.excelforum.com/member.php...o&userid=16816
View this thread: http://www.excelforum.com/showthread...hreadid=320131

Tom,

Try

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Rows.Count 1 Then Exit Sub
Application.EnableEvents = False
Target.EntireRow.Copy Worksheets(Cells(Target.Row,1).Value).Rows(1)
Application.EnableEvents = True
End Sub

This will work as long as the values in column A are limited to being
exactly the same as existing worksheets.

HTH,
Bernie
MS Excel MVP

"tomc112" wrote in message
...

I have 4 sheets is there a way to make the code choose a sheet? I tried
if then else statements but I'm not a very good programmer and I
probably did something wrong.

I figured it would look something like this.

If Target.Rows.Count 1 Then Exit Sub
Application.EnableEvents = False
If column A (In selected Row) = Inventory then
Target.EntireRow.Copy Worksheets("Inventory").Rows(1)
If column A = RawMat1 then
Target.EntireRow.Copy Worksheets("RawMat1").Rows(1)
Application.EnableEvents = True

The problem is I think if you have multiple Inventory the code gets
confused. I only want the row that was selected not multiples.


--
tomc112
------------------------------------------------------------------------
tomc112's Profile:
http://www.excelforum.com/member.php...o&userid=16816
View this thread: http://www.excelforum.com/showthread...hreadid=320131

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Rows.Count 1 Then Exit Sub
Application.EnableEvents = False
Target.EntireRow.Copy Worksheets(Cells(Target.Row,1).Value).Rows(1)
Application.EnableEvents = True
End Sub

This worked great except when I click on a blank cell I get......

Run-Time error '9':

Subscript out of range


--
tomc112
------------------------------------------------------------------------
tomc112's Profile: http://www.excelforum.com/member.php...o&userid=16816
View this thread: http://www.excelforum.com/showthread...hreadid=320131

Tom,

Then change

If Target.Rows.Count 1 Then Exit Sub
Application.EnableEvents = False

to

If Target.Rows.Count 1 Then Exit Sub
If Cells(Target.Row,1).Value = "" Then Exit Sub
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False

HTH,
Bernie
MS Excel MVP


"tomc112" wrote in message
...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Rows.Count 1 Then Exit Sub
Application.EnableEvents = False
Target.EntireRow.Copy Worksheets(Cells(Target.Row,1).Value).Rows(1)
Application.EnableEvents = True
End Sub

This worked great except when I click on a blank cell I get......

Run-Time error '9':

Subscript out of range


--
tomc112
------------------------------------------------------------------------
tomc112's Profile:
http://www.excelforum.com/member.php...o&userid=16816
View this thread: http://www.excelforum.com/showthread...hreadid=320131