how do you move a row based on info in a column?

**juniperjacobs** · 09-21-2010, 10:30 AM

Hi,
i have a sheet where when something becomes invoiced an "S" goes into column R. When the "S" is entered into column R i want that from A:AC of that row to get cut and move into ideally a different workbook, but if that's too complicated then just a tab called "Invoiced History" in the same workbook.

I would want a macro, and I tried writing one but it only cut and moved the first row of the data.

thanks.

**Richard Buttrey** · 09-21-2010, 11:03 AM

Hi,

I think the approach I'd take is create a macro which:

a) Filters the data on column R for the value 'S'.
b) Opens up the archiving workbook.
c) Cut and paste the whole of the filtered cells from A??:AC?? into the next available row in the archiving workbook.
d) Save and close the archiving workbook.
e) Delete the A??:AC?? cells, or the whole row if AC is the last column.
f) Removes the filter.

HTH

**juniperjacobs** · 09-21-2010, 11:12 AM

Conceptually I totally get what you're saying, I just can't seem to execute.

May I have the actual code?

**juniperjacobs** · 09-22-2010, 11:20 AM

i think i figured it out, the only thing is that it requires the invoiced tab to have at least 2 rows of data and i'm not sure why:
Sub MoveClosed()
Sheets("Picks").Select
Selection.AutoFilter Field:=1, Criteria1:="S"
Range("A2:Z2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Invoiced").Select
Range("A1").Select
' The next 3 steps require that there be at least two rows of data in Completed
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Sheets("Picks").Select
Application.CutCopyMode = False
Selection.EntireRow.Delete
Selection.AutoFilter Field:=1
ActiveCell.Select
End Sub