Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Location: India
Posts: 11
Unhappy Shifting rows to sheet2 using Macro

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
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 29
Default Shifting rows to sheet2 using Macro

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
  #3   Report Post  
Junior Member
 
Location: India
Posts: 11
Default

Quote:
Originally Posted by Cimjet[_4_] View Post
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
  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 29
Default Shifting rows to sheet2 using Macro

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
  #5   Report Post  
Junior Member
 
Location: India
Posts: 11
Smile

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


  #6   Report Post  
Junior Member
 
Location: India
Posts: 11
Smile

Quote:
Originally Posted by Rajesh Bhapkar View Post
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 :)
  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 29
Default Shifting rows to sheet2 using Macro

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
copy data from sheet2 to sheet1 when sheet2 has variable # of rows Anne Excel Discussion (Misc queries) 6 February 27th 09 10:48 PM
macro to print sheet2 without open sheet2 ramzi Excel Discussion (Misc queries) 1 January 28th 09 01:07 PM
Deleting Rows and Shifting Up - Repost D.Parker Excel Discussion (Misc queries) 14 October 11th 07 04:58 AM
Shifting rows into columns biggg_fish Excel Discussion (Misc queries) 2 March 28th 07 03:13 AM
Shifting Date from all data on one row to several rows ChuckW Excel Discussion (Misc queries) 1 June 8th 05 07:56 PM


All times are GMT +1. The time now is 06:30 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"