PC Review


Reply
Thread Tools Rate Thread

Re: Shifting rows to sheet2 using Macro

 
 
Cimjet
Guest
Posts: n/a
 
      10th Aug 2012
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
 
Reply With Quote
 
 
 
 
Cimjet
Guest
Posts: n/a
 
      12th Aug 2012
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
 
Reply With Quote
 
 
 
 
Cimjet
Guest
Posts: n/a
 
      13th Aug 2012
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 With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
copy data from sheet2 to sheet1 when sheet2 has variable # of rows Anne Microsoft Excel Misc 6 27th Feb 2009 10:48 PM
macro to print sheet2 without open sheet2 ramzi Microsoft Excel Misc 1 28th Jan 2009 01:07 PM
A1 Sheet2 is linked to A1 sheet1 so that user enters value(abc123) a1 sheet1 and A1 sheet2 is updated pano Microsoft Excel Programming 2 28th Oct 2007 03:32 PM
Sheets(2) = 2nd tab? = Sheet2 (Sheet2)? Max Microsoft Excel Programming 22 5th Aug 2007 10:58 AM
how do copy "sheet1!A1+1 in sheet2 to sheet 3 and get "sheet2!A1+ =?Utf-8?B?RGFueQ==?= Microsoft Excel Misc 5 16th Apr 2007 03:27 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 01:20 AM.