Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Arrow keys move screen instead of moving from cell to cell. | Setting up and Configuration of Excel | |||
Moving through sheets | Excel Discussion (Misc queries) | |||
Copy values from Sheet1 to Sheet2 | Excel Discussion (Misc queries) | |||
Moving page up and down with arrow keys instead of individual cel. | Excel Discussion (Misc queries) | |||
Macro for moving sheets | Excel Discussion (Misc queries) |