+ Reply to Thread
Results 1 to 8 of 8

I need to automate the cutting and pasting of completed tasks between sheets

  1. #1
    Registered User
    Join Date
    05-01-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    4

    I need to automate the cutting and pasting of completed tasks between sheets

    Hello all,

    I need my spreadsheet to move entire rows when a single cell has the word complete OR deferred in it.

    The word complete OR deferred will be in column J - Cant give out the actual Spreadsheet as it has confidential info on it but here is a blank on with basics on it.
    Test_Move_Spreadsheet.xlsx

    Please help me - I'm not very good with VBA/Macros

    Thanks,
    Tom

  2. #2
    Registered User
    Join Date
    01-28-2013
    Location
    Coventry
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: I need to automate the cutting and pasting of completed tasks between sheets

    Hi. If the words 'complete' or 'defered' are always in Column J then it makes the macro easier to write.

    From what you are saying, if Column J = 'Complete' move the entrie row somewhere.
    If Column J = 'Defered', move the entire column somewhere else.

    You have not defined where you want them to be moved ?

  3. #3
    Registered User
    Join Date
    05-01-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: I need to automate the cutting and pasting of completed tasks between sheets

    Quote Originally Posted by a8015945 View Post
    Hi. If the words 'complete' or 'defered' are always in Column J then it makes the macro easier to write.

    From what you are saying, if Column J = 'Complete' move the entrie row somewhere.
    If Column J = 'Defered', move the entire column somewhere else.

    You have not defined where you want them to be moved ?
    Hey,

    If you look at thew attached excel file i have marked the pages 'move from' 'move to'

    So both move from one to the other page.

  4. #4
    Registered User
    Join Date
    01-28-2013
    Location
    Coventry
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: I need to automate the cutting and pasting of completed tasks between sheets

    This should work

    Please Login or Register  to view this content.
    NOTE there are some contraints to this macro.
    1 It will only run through until line 31 (this can be changed to what ever value you choose simply by changing the Do Until r = 31+1 bit of the code to for example Do Until r = 64 +1)
    2 It will only copy rows with the words 'complete' or deferred' in Column J (These words ARE case sensitive, but can be changed in the code also)

    Try it out and let me know how you get on

  5. #5
    Registered User
    Join Date
    05-01-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: I need to automate the cutting and pasting of completed tasks between sheets

    Quote Originally Posted by a8015945 View Post
    This should work

    Please Login or Register  to view this content.
    NOTE there are some contraints to this macro.
    1 It will only run through until line 31 (this can be changed to what ever value you choose simply by changing the Do Until r = 31+1 bit of the code to for example Do Until r = 64 +1)
    2 It will only copy rows with the words 'complete' or deferred' in Column J (These words ARE case sensitive, but can be changed in the code also)

    Try it out and let me know how you get on
    This is PERFECT!
    However, can you make it so it cuts the row rather than copy?

    Thanks for your time its greatly appreciated

  6. #6
    Registered User
    Join Date
    05-01-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: I need to automate the cutting and pasting of completed tasks between sheets

    Also can i be done so it always checks and moves any i have completed / deferred?

  7. #7
    Registered User
    Join Date
    01-28-2013
    Location
    Coventry
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: I need to automate the cutting and pasting of completed tasks between sheets

    yeah, try rows(r).Cut instead of rows(r).Copy

    Just note though, sometimes it is better to keep an original copy of everything, just incase things go wrong somewhere, thats why I chose .copy to begin with, so you can view the ones you want on their own, as well as how they fit in to the bigger picture.

  8. #8
    Registered User
    Join Date
    01-28-2013
    Location
    Coventry
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: I need to automate the cutting and pasting of completed tasks between sheets

    the macro will not auto update everytime there is a change, it needs to be called everytime.

    see edited code

    Please Login or Register  to view this content.
    Now go to Sheet1, insert shape, any shape will do but rectangles are usually more professional. Set the formatting (colour, size, outline etc). When done right click the shape, ASSIGN MACRO and select the macro name (move_one_page_to_other)

    Now everytime you make a change, press the button. This will clear Sheet2, search Sheet1 for 'confirmed' or 'deferred' and move info to Sheet2.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1