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

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 ?

Originally Posted by a8015945

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.

This should work

Sub move_one_page_to_other()
r = 2
s = 2
Sheets(1).Select

Do Until r = 31 + 1
If Cells(r, 10) = "complete" Or Cells(r, 10) = "deferred" Then
Rows(r).Copy
Sheets(2).Select
Rows(s).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

s = s + 1
Sheets(1).Select
End If
r = r + 1

Loop
Sheets(2).Select

End Sub

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

Originally Posted by a8015945

This should work

Sub move_one_page_to_other()
r = 2
s = 2
Sheets(1).Select

Do Until r = 31 + 1
If Cells(r, 10) = "complete" Or Cells(r, 10) = "deferred" Then
Rows(r).Copy
Sheets(2).Select
Rows(s).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

s = s + 1
Sheets(1).Select
End If
r = r + 1

Loop
Sheets(2).Select

End Sub

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

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

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.

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

see edited code

Sub move_one_page_to_other()
r = 2
s = 2
Application.ScreenUpdating = False
Sheets(2).Select
Range("A2:N31").Select
Selection.ClearContents
Range("A1").Select
Sheets(1).Select

Do Until r = 31 + 1
If Cells(r, 10) = "complete" Or Cells(r, 10) = "deferred" Then
Rows(r).Copy
Sheets(2).Select
Rows(s).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

s = s + 1
Sheets(1).Select
End If
r = r + 1

Loop
Sheets(2).Select

End Sub

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.