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

[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....

 

[Claus Busch]

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

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.

 

[Betka]

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.

 

[Claus Busch]

Hi,

Am Fri, 26 Sep 2014 11:58:40 -0700 (PDT) schrieb Betka:

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.

have a look:
https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for "MoveRows".
Download the file because macros are disabled in OneDrive.


Regards
Claus B.