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

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

Thank you for your reply....
It works for copying but after copying i want to delete the row from the original cell to avoid duplication and the macro should run automatically every time the sheet is open

On Saturday, August 11, 2012 10:09:04 AM UTC-4, Rajesh Bhapkar wrote:
'Cimjet[_4_ Wrote:

;1604493']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



Thank you for your reply....

It works for copying but after copying i want to delete the row from the

original cell to avoid duplication and the macro should run

automatically every time the sheet is open



Rajesh Bhapkar

Hi
Here is the script, it will delete the rows after copying over.
I'm not sure exactly what you want when you say "every time the sheet is open"
So don't place this script in a module, place it in This Workbook<
It will run every time you open that file.

Option Explicit
Private Sub Workbook_Open()
Dim sh2 As Worksheet, finalrow As Long
Dim i As Long, lastrow As Long
Set sh2 = Sheets("Sheet2")
finalrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To finalrow
If Cells(i, 21).Value = "Completed" Then
lastrow = sh2.Cells(Cells.Rows.Count, 1).End(xlUp).Row
Cells(i, 1).EntireRow.Copy Destination:=sh2.Cells(lastrow + 1, 1)
Cells(i, 1).EntireRow.Delete
End If
Next i
End Sub

Thank you for your help, actually i figured it out and implemented...Thank you so much

For every time it opens, that is when anyone opens the sheet the macro should run automatically.

and i wanted the macro for sheet1 only... So i placed the script in sheet1 and placed an another macro in workbook to call the macro in sheet1 every time it opens...
Hope this makes it clear...


Thank you again :)

On Monday, August 13, 2012 2:13:27 AM UTC-4, Rajesh Bhapkar wrote:
Thank you for your help, actually i figured it out and

implemented...Thank you so much









--

Rajesh Bhapkar

You're welcome
Thanks for the feedback
Cimjet