Re: Shifting rows to sheet2 using Macro

Discussion in 'Microsoft Excel New Users' started by Cimjet, Aug 10, 2012.

  1. Cimjet

    Cimjet Guest

    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
     
    Cimjet, Aug 10, 2012
    #1
    1. Advertisements

  2. Cimjet

    Cimjet Guest

    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
     
    Cimjet, Aug 12, 2012
    #2
    1. Advertisements

  3. Cimjet

    Cimjet Guest

    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
     
    Cimjet, Aug 13, 2012
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jack

    Column : transfer from sheet1 to sheet2

    Jack, Mar 23, 2004, in forum: Microsoft Excel New Users
    Replies:
    1
    Views:
    447
    Bernie Deitrick
    Mar 23, 2004
  2. Guest
    Replies:
    2
    Views:
    267
    Don Guillett
    Jun 5, 2004
  3. Guest
    Replies:
    1
    Views:
    188
    Dave Peterson
    Jan 26, 2007
  4. Compare and Update elements from Sheet1 with Sheet2

    , Nov 26, 2007, in forum: Microsoft Excel New Users
    Replies:
    3
    Views:
    210
  5. Caz22

    I want to extract info from a database on sheet1 to sheet2.

    Caz22, Jul 2, 2008, in forum: Microsoft Excel New Users
    Replies:
    6
    Views:
    2,014
    Gary Brown
    Jul 7, 2008
Loading...

Share This Page