Macro to select, move to a new location and delete the selected cell

[plumberef]

Looks simple but 1 step is beyond me. Hope that you can help me.

Need a macro to select the rows with "Y" from row 5 to row 50, move these rows to another location, delete the original selected rows with "Y".

See attachment for more infor.

move comlpeted tasks.jpg

The step I am having problem with is:
* how to tell it where to paste the selected rows in unused rows

Many thanks.

[NeedForExcel]

So every time the Macro runs, do you want the rows that are already there with Y on the new location to be deleted?

Or do you want to paste to the next available row?

[plumberef]

As per the picture, move the Y rows to a new location, delete the original Y rows.

I think the picture describes it better than words. Ha.

[NeedForExcel]

Maybe you did not get my question.

Assume that the code is being run for the second time, what should happen to the Y rows that were filtered out when the macro was run previously and pasted at a new location.

Should those be overwritten, or should be kept and new Y rows should be added below?

[plumberef]

2 tables

Table 1 (with yellow header) and Table 2 (the one to the right of Table 1)

Move the 3 cells row with Y in Table 1 to Table 2. Delete the selected Y row in Table 1.

Then I should have Table 1 with no row with Y.

Table 2 should have the old Y rows and newly added Y rows.

Hope the above is clear now.

[NeedForExcel]

Try this

Sub DeleteCopyRowsWithY()
    
    Dim R
    
    Application.ScreenUpdating = False
    
    With Range("A1")
        .AutoFilter Field:=3, Criteria1:="=Y"
        .CurrentRegion.SpecialCells(xlCellTypeVisible).Select
    End With
    
    Selection.Copy Range("E1")
    
    R = Range("E1").CurrentRegion: Range("E1").CurrentRegion.ClearContents
    
    Application.CutCopyMode = False
    
    Rows("2:50").Select: Selection.Delete Shift:=xlUp
    
    Selection.AutoFilter
    
    Range("E1").Resize(UBound(R), 3).Value = R: Range("A1").Activate
    
    Application.ScreenUpdating = True
    
End Sub

See the file attached.

[plumberef]

Many many thanks.

1 issue. How to move the Y rows to the next unused row in Table 2?

It is now replacing the used rows. I want to keep the old Y rows in Table 2.

Thanks.

[NeedForExcel]

Here!

Sub DeleteCopyRowsWithY()
    
    Dim R
    
    Application.ScreenUpdating = False
    
    With Range("A1")
        .AutoFilter Field:=3, Criteria1:="=Y"
        .CurrentRegion.Offset(1).SpecialCells(xlCellTypeVisible).Select
    End With
    
    Range("A1:C1").Copy Range("E1")
    
    Selection.Copy Cells(Range("E1").Cells(Rows.Count, 1).End(xlUp).Row + 1, 5)
    
    R = Range("E1").CurrentRegion: Range("E1").CurrentRegion.ClearContents
    
    Application.CutCopyMode = False
    
    Rows("2:50").Select: Selection.Delete Shift:=xlUp
    
    Selection.AutoFilter
    
    Range("E1").Resize(UBound(R), 3).Value = R: Range("A1").Activate
    
    Application.ScreenUpdating = True
    
End Sub

[NeedForExcel]

That was exactly what I was asking in Post #2 & #4..

I will change the code and give you.

[NeedForExcel]

EDIT: Post Removed By NFE