Windows 7 - Shifting rows to sheet2 using Macro

Asked By Rajesh Bhapkar on 10-Aug-12 12:53 AM
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




Cimjet replied to Rajesh Bhapkar on 10-Aug-12 08:23 AM
Hi
See link attached :
http://cjoint.com/?3HkovuGpswV
it is a sample file, maybe you can adapt to your needs.
Cimjet
Rajesh Bhapkar replied to Cimjet on 11-Aug-12 10:09 AM
'Cimjet[_4_ Wrote:

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
Cimjet replied to Rajesh Bhapkar on 12-Aug-12 08:20 AM
Hi
Here is the script, it will delete the rows after copying over.
I am not sure exactly what you want when you say "every time the sheet is open"
So do not 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
Rajesh Bhapkar replied to Cimjet on 13-Aug-12 02:13 AM
Thank you for your help, actually i figured it out and
implemented...Thank you so much




--
Rajesh Bhapkar
Cimjet replied to Rajesh Bhapkar on 13-Aug-12 07:09 AM
You're welcome
Thanks for the feedback
Cimjet