Copy row to other sheet based on cell value

[srh5454]

So, i can't figure out how to copy an entire row to a different page based on the entry in one cell.

Like, if the cell value is 2 the row goes to sheet 2, if the cell value is 3 the row goes to sheet 3. I'de really like to use words and a drop down menu though... but think that will be considerably more confusing.

It's for a number of logs. ie sheet 1 is the master log, and the other sheets are just status tracking logs.

thank you,
srh5454

 

[HalfAce]

if the cell value is 2 the row goes to sheet 2, if the cell value is 3 the row goes to sheet 3. I'de really like to use words and a drop down menu though

Hello srh5454, welcome to the board.
Does this mean you want a dropdown that displays "One" "Two" "Three" (etc) and activates
"Sheet1", "Sheet2" or "Sheet3" when selected(?), or can you have the real sheet names
displaying in your dropdown?

 

[daniels012]

From the Menu toolbar you use >Data >Autofilter
In your example: set the value from the cell value you want to 2 and that will show only rows with that value and you can do your copy that you desire.

Michael

 

[srh5454]

well, i want the dropdown to say "issued", "received", "in progress", "awaiting parts","paperwork in acct." and "complete" but the sheet names can also have those same names. so yes, the dropdown menu can match the sheet names.
srh5454

 

[HalfAce]

OK, here's a quick little example:
(Goes into the sheet module for the sheet of interest)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
On Error Resume Next
ActiveCell.EntireRow.Copy Sheets(Target.Value).Cells(Rows.Count, "A").End(xlUp)(2)
If Err.Number <> 0 Then _
  MsgBox "The sheet '" & Target.Value & "' can not be found.", , "Destination Sheet Error"
On Error GoTo 0
End Sub

Note, this example assumes your dropdowns (data validation cells) are in column A and
your sheet names match the dropdown choices.
It only copies the active cell's row now, so my question would be how will you determine
which row(s) you want to copy?

 

[srh5454]

hmm, and i've noticed that if you change the drop down menu (and thus the sheet it appears on, it stays on the last sheet and also appears on the new sheet) is there a way to make it only be on one sheet(not including the first main one) at a time?

 

[srh5454]

to answer your question: well it's for a service department, so as the job progresses, i will change the drop down menu as i do the work. ie: i issue a repair number, then i receive the part, then i do the work, then i send the paperwork to our accting department, then shipping comes and gets it. and as these steps are taken i change the dropdown menu to a different step and then i can use the data on the seperate sheets to more easily calculate the percentages of parts in different stages of repair/processing.

 

[srh5454]

someone told me that i could use macro for this, but i have no idea how there either.

 

[HalfAce]

is there a way to make it only be on one sheet(not including the first main one) at a time?

If I understand, you can just change the line:
ActiveCell.EntireRow.Copy. . .
to: ActiveCell.EntireRow.Cut. . .
This will make it so that data is only on one sheet at a time.

someone told me that i could use macro for this, but i have no idea how there either.

They're right. A macro is what we're using here.
To get it to work, right click on the sheet tab for the sheet(s) of interest and choose View code.
Copy & paste this code into the white area that is the sheet's code module.
Press Alt+Q to close the vb editor and get back to your sheet.
Repeat this for every sheet you want it to work on.

Now, I'm not sure if you want the data on one sheet (only) at a time (if so doing the steps
above should get you going.)
If you want the data to remain on a 'main' sheet somewhere as well as going back
& forth on the 'other' sheets, then in the 'main' sheet's code module, don't make the change
from ActiveCell.EntireRow.Copy. . .
to ActiveCell.EntireRow.Cut. . .

Does this help answer your question or am I misunderstanding?