On Friday, August 10, 2012 12:53:08 AM UTC-4, Rajesh Bhapkar wrote:
Hi, I am trying to use macro to shift cells from one sheet to another

once the status of the tasks is changed to completed.



I want the program to do the following

Look in column U to find the status completed.

Then Select the complete row, Copy it and paste into another sheet which

is completed tasks 2012 in the blank row after the last filled row

And then delete the cell from the first sheet (that is task list)



I tried but i am not able to work out how to look for the next blank row

in sheet 2 for pasting and how to loop the program till all rows with

completed status are shifted to the next sheet.



Kindly help

This is what i figured out but not working the way i want

Sub Auto_Open()

'

' Auto_Open Macro

'



'

Columns("U:U").Select

Selection.Find(What:="Completed", After:=ActiveCell,

LookIn:=xlFormulas, _

LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,

_

MatchCase:=False, SearchFormat:=False).Activate

Rows(ActiveCell).Select

Selection.Copy

Sheets("Completed Tasks 2012").Select

ActiveSheet.Paste

Sheets("Task List").Select

Application.CutCopyMode = False

Selection.Delete Shift:=xlUp

Columns("U:U").Select

Selection.FindNext(After:=ActiveCell).Activate

Rows(ActiveCell).Select

Selection.Copy

Sheets("Completed Tasks 2012").Select

Rows("99:99").Select

ActiveSheet.Paste

Sheets("Task List").Select

Application.CutCopyMode = False

Selection.Delete Shift:=xlUp

Columns("U:U").Select

Selection.FindNext(After:=ActiveCell).Activate

Selection.FindNext(After:=ActiveCell).Activate

Rows("230:230").Select

Selection.Copy

Sheets("Completed Tasks 2012").Select

Rows("100:100").Select

ActiveSheet.Paste

Sheets("Task List").Select

Application.CutCopyMode = False

Selection.Delete Shift:=xlUp

End Sub









--

Rajesh Bhapkar

Hi
See link attached :
http://cjoint.com/?3HkovuGpswV
It's a sample file, maybe you can adapt to your needs.
Cimjet