How do I move entire row from one excel sheet to another

  • Thread starter Thread starter Beata Tassone
  • Start date Start date
B

Beata Tassone

Hi,

I need help with this one. I have a workbook with 3 schedules (Bab.Sch, Fab..Sch and Shipped. Each containing

JOB#, CUSTOMER, WORK, DESCRIPTION, DUE DATE, STATUS, WORK TYPE. in columns A, B, C, D, E, F, G

Status field is a pull down list with few options ("Shipped" is one of them). I need a macro that will cut the entire row from Bab.Sch and Fab.Sch when "Shipped" option is selected and move it to the Shipped tab/sheet. I hopethat makes sense. I have tried looking up answers online with no luck. Please help....
 
Ciao Beata,

Am Fri, 26 Sep 2014 05:40:34 -0700 (PDT) schrieb Beata Tassone:
JOB#, CUSTOMER, WORK, DESCRIPTION, DUE DATE, STATUS, WORK TYPE. in columns A, B, C, D, E, F, G
Click to expand...

insert following code into the code window of "ThisWorkbook":

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Dim arrOut As Variant

If Intersect(Target, Sh.Range("F:F")) Is Nothing Or Sh.Name _
= "Shipped" Or Target.Count > 1 Then Exit Sub
With Target
If .Value = "Shipped" Then
arrOut = Range(Cells(.Row, 1), Cells(.Row, 7))
Sheets("Shipped").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) _
.Resize(1, 7) = arrOut
Rows(.Row).Delete
End If
End With
End Sub


Regards
Claus B.
 
So I posted it and got a got a syntax error. Sorry I know this is probably really simple but this VBA out of my field.
 
Back
Top