Move data from a sheet to another

[Guest]

Hello,

The sheet I have has 5 columns and the 5th is for status, IN or OUT.

What I want is to MOVE the row to another worksheet in the same workbook IF
the status is OUT.

TIA

Hernan

 

[Ron de Bruin]

Hi Hernan

You can use Autofilter on the column and copy the visible data to another
sheet

Activate AutoFilter:

Select a cell in your data table and use Data>Filter>AutoFilter to activate
AutoFilter.
Tip: Shortcut for the English version is Alt d f f

In each header cell a dropdown will appear next to your field name.
Click on the dropdown in the status field and choose OUT.

Copy the filter result :

1) Be sure that the active cell is in the data range
2) Press Ctrl * to select all data or use F5>Special>Current region>OK
3) Ctrl c or Edit>Copy
4) Insert>Worksheet
5) Ctrl v or Edit>Paste
6) Select the sheet with the filter
7) Press Esc
8) Press Alt d f f or Data>Filter>AutoFilter to turn off AutoFilter


Or use my filter add-in
http://www.rondebruin.nl/easyfilter.htm

 

[Gord Dibben]

You could use formulas in 5 columns on the target sheet that would show the
information if status is OUT

Assuming your source sheet is named "Source"(no quotes)

In A1 of target sheet =IF(Source!$E1="OUT",Source!A1,"")

Drag across to E1 and down to wherever.

To actually "move" the data without formulas would require VBA


Gord Dibben MS Excel MVP

 

[Guest]

If you do not want any blank cells in between, then try this:

In Sheet 2
A1
=IF(ISERR(SMALL(IF(Sheet1!$E$1:$E$7="Out",ROW(INDIRECT("1:"&ROWS(Sheet1!$A$1:$A$7)))),ROWS($1:1))),"",INDEX(Sheet1!$A$1:$A$7,SMALL(IF(Sheet1!$E$1:$E$7="Out",ROW(INDIRECT("1:"&ROWS(Sheet1!$A$1:$A$7)))),ROWS($1:1))))

Adjust your range to suit
ctrl+shift+enter (not just enter)
copy down as far as need

 

[Guest]

I have not tried this, but I am assuming it works and great!

However, what I am looking for is to do this automagically, so most likely
this will have to be done with VBA.

Sorry for my late response, you guys are great!

Thanks for the great help.

Hernan.