Forum Discussion
How do I move data completely from one sheet to another once it meets a certain criteria?
Hi there!
I'm trying to format a tracking sheet I'm working on!
I want to pull a row (for example a task) from sheet 1 to sheet 2 once it's complete.
EX: Column K is my "Final Status" column, and once I input "Completed" I'd like to to pull the entire row to sheet two. So it would completely remove it from sheet one. I only want to see in progress tasks on sheet one and completed tasks on sheet 2.
Any ideas?
Thank you!
With your permission, if I may recommend, add a file (without sensitive data) and use this file to describe your problem step by step, or add photos with the appropriate description.
You'll get a quicker and more accurate answer, and those who want to help don't have to invest time guessing the problem.
Always inform about your Excel version, operating system, storage medium/hard drive, OneDrive, Sharepoint, etc.).
Don't forget no one in this forum can see what you see.
In this link you will find some more information about it:
Welcome to your Excel discussion space!
Thank you for your understanding and patience
I know I don't know anything (Socrates)
A little late but I figured WHY NOT..............
Just Replace the Purple highlighted sections below with the keywords you want.
(Replace Sheet 1 with your 1st sheets name, Replace Sheet 2 with your 2nd sheets name. The word "Completed" is the trigger word that is checked against-Could also be "DONE" or "CLOSED", and the K2:K section is the Range of where to look for the trigger word. )
I use this macro often.
Sub CheezyBeans()
'Updated by Kutools for Excel 2017/8/28
Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim K As Long
I = Worksheets("Sheet1").UsedRange.Rows.Count
J = Worksheets("Sheet2").UsedRange.Rows.Count
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Sheet2").UsedRange) = 0 Then J = 0
End If
Set xRg = Worksheets("Sheet1").Range("K2:K" & I)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.Count
If CStr(xRg(K).Value) = "Completed" Then
xRg(K).EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A" & J + 1)
xRg(K).EntireRow.Delete
If CStr(xRg(K).Value) = "Completed" Then
K = K - 1
End If
J = J + 1
End If
Next
Application.ScreenUpdating = True
End Sub
