COPY & PASTE by Value

[JeanPyerC]

Hi Teams!

I am trying to create a macro where it will copy values based on criteria in column 5, then move to column 7.

So far the only good formula I got is to copy the entire row but I don't need the entire row.

Is there a way for Macro to go to the Value, Select 3 Cells from the left, and xlToLeft and Select 3 cells from Right?

Private Sub COPY()
a = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To a
If Worksheets("Sheet1").Cells(i, 5).Value = "EXPIRED" Then
Worksheets("Sheet1").Rows(i).COPY
'Range(Selection, Selection.End(xlToLeft)).Select
Worksheets("Sheet2").Activate
b = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet2").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("Sheet1").Activate
End If
Next
Application.CutCopyMode = False
ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Select
End Sub


Photo of What I am trying to copy. Thank You Guys!

Sheet1

Sheet2

 

[My Aswer Is This]

So we are looking for expired in column 5
Then copy values in column A to column B
And column 3 to what?

I see no Status or Equipment column in sheet 1

Tell me what columns you want copied from sheet 1 to sheet 2