COPY & PASTE by Value

JeanPyerC

New Member
Joined
Apr 5, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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
1617680596539.png


Sheet2
1617680743784.png
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,581
Office Version
  1. 2013
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,930
Messages
5,639,051
Members
417,066
Latest member
rhenman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top