Hi, I am trying to create a macro that does the following:
copies rows from a worksheet called "Action Items" to a worksheet called "Completed Action Items"
The rows to be copied will have the word "Complete" in column A.
After the rows have been copied to the "Completed Action Items" sheet I would like them removed from the "Action Items" worksheet.
This is what I have so far - but for some reason it doesn't seem to filter by the word "Complete" in column A. Also, the "selection.Cut" isn't working because it is selecting multiple ranges (after applying the autofilter).
Any ways around this?
I'm not sure if the rest of the marco after the selection.cut is correct or not - just my best/first guess.
I have attached an example workbook. I am also trying to do the same thing with cancelled action items. I would like them to move to the Cancelled Action Item sheet.
Thanks so much ahead of time! This will be a huge time saver for me and my team.
Sub PasteRowDelete()
Application.ScreenUpdating = False
Worksheets("Action Items").Select
'Select Range
'(note you can change this to meet your requirements)
Range("A3:J300").Select
'Apply Autofilter
Selection.AutoFilter
FilterCriteria = Complete
'NOTE - this filter is on column A (field:=1), to change
'to a different column you need to change the field number
Selection.AutoFilter field:=1, Criteria1:=FilterCriteria
'Select the visible cells (the filtered data)
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Cut
Sheets("Complete Action Items").Select
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
'Clear the clipboard contents
Application.CutCopyMode = False
'Go back to the original tab
Sheets("Action Items").Select
'Clear the autofilter
Selection.AutoFilter field:=1
'Take the Autofilter off
Selection.AutoFilter
Selection.Delete Shift:=xlUp
ActiveCell.Select
Application.ScreenUpdating = True
End Sub
Bookmarks