Excel VBA - Copy and Paste Values Based on Criteria Within Same Table on Same Sheet

[ranken777]

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

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>

 

[My Aswer Is This]

Try This:

Sub Test()
Application.ScreenUpdating = False
Dim i As Integer
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To Lastrow
        If Cells(i, 1).Value = "Automatic" Then
            Cells(i, 4).Offset(, -2).Resize(, 2).Value = Cells(i, 4).Value
        End If
    Next
Application.ScreenUpdating = True
End Sub

 

[ranken777]

My Answer Is This, thank you for your response. I implemented and executed your example and I did not observe the cells being copied and pasted upon execution of the script. Did you try your example within Excel? If it works for you, it is probably User error on my part.

 

[My Aswer Is This]

Yes I always test my scripts.
The script looks in column "A" if it finds the word "Automatic" it
Copies the text in column "D" into columns "B" and "C"
What version of Excel are you using?
Are you using an "Apple" Computer.
If also assumes only the word "Automatic" is in column "A" not
The Automatic coffee pot is broke.

 

[ranken777]

Your solution worked perfectly. My first attempt must have been user error! Thank you so much!

 

[My Aswer Is This]

Glad I was able to help you. Check back in the next time you need some help.