Move data from a sheet to another
 

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

Move data from a sheet to another
 

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 DataFilterAutoFilter 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 F5SpecialCurrent regionOK
3) Ctrl c or EditCopy
4) InsertWorksheet
5) Ctrl v or EditPaste
6) Select the sheet with the filter
7) Press Esc
8) Press Alt d f f or DataFilterAutoFilter to turn off AutoFilter


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



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Hernan" wrote in message
...
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

Move data from a sheet to another
 

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

On Sun, 3 Dec 2006 10:55:01 -0800, Hernan
wrote:

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

Move data from a sheet to another
 

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(INDI RECT("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


"Hernan" wrote:

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

Move data from a sheet to another
 

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.

"Teethless mama" wrote:

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(INDI RECT("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


"Hernan" wrote:

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