Per the simplified example table below, I am looking for an efficient way to copy the value of individual cells of Column D to both Column B and Column C if Column A = "Automatic". The actual table itself may be many thousands of rows.
I need the pasted cells to be the "values" only as Column D is formulated and I want only static values to be pasted.
I have tried looping through rows as well as the preferred Auto Filter method, but I can't seem to get it to work.
Any assistance would be much appreciated.
The code below would meet my needs; although it isn't the preferred Auto Filter method. My only problem is that it does not paste "values" only.
Sub CopyAutomaticValues()
Dim c As Range, LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
For Each c In Range("A1:A" & LastRow)
If c.Value = "Automatic" Then Range("D" & c.Row).Copy Range("B" & c.Row & ":C" & c.Row)
Next c
Application.ScreenUpdating = True
End Sub
TABLE BEFORE SCRIPT IS EXECUTED
<tbody>
</tbody>
TABLE AFTER SCRIPT IS EXECUTED (Desired Results)
<tbody>
</tbody>
I need the pasted cells to be the "values" only as Column D is formulated and I want only static values to be pasted.
I have tried looping through rows as well as the preferred Auto Filter method, but I can't seem to get it to work.
Any assistance would be much appreciated.
The code below would meet my needs; although it isn't the preferred Auto Filter method. My only problem is that it does not paste "values" only.
Sub CopyAutomaticValues()
Dim c As Range, LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
For Each c In Range("A1:A" & LastRow)
If c.Value = "Automatic" Then Range("D" & c.Row).Copy Range("B" & c.Row & ":C" & c.Row)
Next c
Application.ScreenUpdating = True
End Sub
TABLE BEFORE SCRIPT IS EXECUTED
COLUMN A | COLUMN B | COLUMN C | COLUMN D |
Manual | Red | ||
Automatic | Green | ||
Automatic | Yellow | ||
Manual | Black | ||
Manual | Grey |
<tbody>
</tbody>
TABLE AFTER SCRIPT IS EXECUTED (Desired Results)
COLUMN A | COLUMN B | COLUMN C | COLUMN D |
Manual | Red | ||
Automatic | Green | Green | Green |
Automatic | Yellow | Yellow | Yellow |
Manual | Black | ||
Manual | Grey |
<tbody>
</tbody>