Macro to cut and insert specific cells based on cell value from one tab to another in the same workbook

[Kevin Hyskell]

I need a macro that will consider the value of cells in a specific column, cut them, and insert into a different tab (cutting from tab "Active" and inserting on tab "Complete". The macro will cut the data for all cells in columns A through Y for which the value of column U of the corresponding row is "Complete" on tab "Active". The macro will then INSERT this data beginning at row 5 of the "Complete" tab. When cutting the remaining cells will shift up, likewise the inserted cells will cause the existing populated cells to shift down. Macro will consider data in A5 through Y253.

ie: When clicked, assume U7 and U10-U12 are "Complete" then A7-Y7, A10-Y10, A11-Y11, and A12-Y12 will be cut, the cells below them will shift up to fill the gap, on the "Complete" tab the 4 groups of data will be INSERTED beginning at cell A5, causing the data already existing in A5 to shift down.

I'm fairly excel saavy but I haven't done macros in about 15 years, so rusty doesn't begin to describe it!

I'm grateful in advance for any assistance anyone may be able to provide.

I would happily attach my excel file if I were allowed.... But I can also send via email if anyone wants to take this on.

Thanks,
Kevin

 

[Kevin Hyskell]

I would update the initial post if I could figure out how but,,..... So, I got the macro figured out, including a button and a fun message before executing. New problem: the macro is too fast. The macro finds all "complete" records, copies them, inserts into the
"complete" tab, goes back to the "active" tab, finds all "Complete" records again, deletes them, bada bing. The problem: if there are contiguous records that are complete (ie row 3 and 4, one above the other), it will delete 3 but it will not delete 4. I suspect this is happening because as 3 is deleted, 4 is then shifting to that row which is being ignored. I don't know how to solve this aside from forcing a row of space in between every populated row, but that's not exactly an attractive option. I've written in the code I used to do this below. As always I do appreciate any assistance. P.S. Be gentle, I'm not a vba pro!!

Thanks,
Kevin


Sub CopyConditional()


Dim Answer As String
Dim MyNote As String


MyNote = "Are you sure you want to transfer ALL completed projects?"


Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "Transfer Projects")


If Answer = vbNo Then
MsgBox "Transfer Aborted"
Else




Dim tfCol As Range, Cell As Object


Set tfCol = Range("U5:U255")


For Each Cell In tfCol


If Cell.Value = "Complete" Then
Cell.EntireRow.Copy
Sheets("Complete").Range("A6").Insert
End If


Next


For Each Cell In tfCol


If Cell.Value = "Complete" Then
Cell.EntireRow.Delete
End If


Next


MsgBox "Transferred"
End If


End Sub

 

[Fluff]

Hi & welcome to MrExcel
How about

Sub CopyConditional()

   Dim Answer As String
   Dim MyNote As String
   Dim Cell As Range
   Dim Rng As Range

   MyNote = "Are you sure you want to transfer ALL completed projects?"
   
   Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "Transfer Projects")
   
   If Answer = vbNo Then
      MsgBox "Transfer Aborted"
      Exit Sub
   End If
   
   For Each Cell In Range("U5:U255")
      If LCase(Cell.Value) = "complete" Then
         Cell.EntireRow.Copy
         Sheets("Complete").Range("A6").Insert
         If Rng Is Nothing Then
            Set Rng = Cell
         Else
            Set Rng = Union(Rng, Cell)
         End If
      End If
   Next
   If Not Rng Is Nothing Then Rng.EntireRow.Delete
   
   MsgBox "Transferred"

End Sub