Moving from 1 row sheet1 to sheet2

[tomc112]

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

Let me explain. I will have 2 worksheets Lets say A and B. I
Worksheet A I have rows and rows of data. In worksheet B I want t
create a form (not excels form one written in the spreadsheet itself n
VB) that will contain some of the info from each row. Like fo
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 change
in sheet B. I know this is done in Access very easily with a table an
a form but can it be done in exce

 

[Bernie Deitrick]

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]

I have 4 sheets is there a way to make the code choose a sheet? I trie
if then else statements but I'm not a very good programmer and
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 get
confused. I only want the row that was selected not multiples

 

[Bernie Deitrick]

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]

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 rang

 

[Bernie Deitrick]

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