# How do I copy and paste with VBA (based on condition)?



## DayTraderKevin (Dec 31, 2022)

Is there a way to do this with VBA - if any cell in B2:B4 = "Buy", then copy column A and paste to column C.

In this example, because B3 = "Buy", I'd want to copy A3 and paste to C3


*A**B**C**1*PriceActionBought Price*2*$1.20*3*$4.87Buy*4*$3.22


----------



## 6StringJazzer (Dec 31, 2022)

I have not tested this but it's pretty straightforward.


```
Dim Cell As Range

For Each Cell In Range("B2:B4")

   If Cell = "Buy" Then
      Cells(Cell.Row, "C") = Cells(Cell.Row, "A")
   End If

Next Cell
```


----------



## DayTraderKevin (Dec 31, 2022)

6StringJazzer said:


> I have not tested this but it's pretty straightforward.
> 
> 
> ```
> ...


Will this paste values in column C? Column A will be changing frequently (based on market) but I want to paste the current price in column C at the time of "Buy".


----------



## vcoolio (Dec 31, 2022)

Hello Kevin,

Perhaps this code placed into the worksheet module may do the task for you:-

```
Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Columns(2)) Is Nothing Then Exit Sub
    If Target.Cells.Count > 1 Then Exit Sub
   
    Application.ScreenUpdating = False
    Application.EnableEvents = False
   
    If Target.Value = "Buy" Then
         Target.Offset(, 1).Value = Target.Offset(, -1).Value
    End If
   
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub
```

Once you type "Buy" into any cell in Column B and click away, the Column A value will be copied to the relevant cell in Column C. I'm assuming that you have many entries in Column A, hence the code will cover all of Column B.

To implement this code:-
- Right click on the sheet tab.
- Select "View Code" from the menu that appears.
- In the big white code field that then appears, paste the above code.

This will work a tad quicker if you use data validation drop downs in Column B. Selecting from a drop down will make executing the code virtually instant.

I hope that this helps.

Cheerio,
vcoolio.


----------



## DayTraderKevin (Dec 31, 2022)

vcoolio said:


> Hello Kevin,
> 
> Perhaps this code placed into the worksheet module may do the task for you:-
> 
> ...


Thanks for helping! Some questions because my real spreadsheet is a bit larger than the example I gave...

- Is (2) in your example the column #?
- Is offset -1 and 1 the columns that are in relation to the column with "Buy"?
- Will this paste values in column C? Column A will be changing frequently (based on market) but I want to paste the current price in column C at the time of "Buy".


----------



## vcoolio (Dec 31, 2022)

Hello Kevin,

Yes, (2) refers to Column B, the target column
Offset(, 1) refers to Column C (i.e. one column to the right of Column B, the target column).
Offset(, -1) refers to Column A (i.e. one column to the left of Column B, the target column).
Hence, if your target in Column B is, say, cell B2 (where "Buy" is typed in), then the value in cell B2*.Offset(, 1)*, (i.e. cell C2) is equal to the value of cell B2*.Offset(, -1)*, (i.e. cell A2). It will only work if the "Action" criteria is "Buy". If anything else is typed in, nothing will happen.
If the size or set out of your actual spreadsheet is larger or different to your sample, then upload a sample using the XL2BB function which you can find at the top of any reply box.

*Please test the above code in a copy of your actual workbook.*

I hope that this helps.

Cheerio,
vcoolio.


----------



## DayTraderKevin (Dec 31, 2022)

vcoolio said:


> Hello Kevin,
> 
> Yes, (2) refers to Column B, the target column
> Offset(, 1) refers to Column C (i.e. one column to the right of Column B, the target column).
> ...


It didn't like this line.


----------



## vcoolio (Dec 31, 2022)

I've just tested your modified code and all works as it should. You could try changing the column number to its letter as follows:-


```
If Intersect(Target, Columns("ALY")) Is Nothing Then Exit Sub
```

Other than that, you may have to upload a sample as per my last post. Please note that I'll be leaving shortly for a few days away and won't be back until the end of the week.

Cheerio,
vcoolio.


----------



## DayTraderKevin (Dec 31, 2022)

vcoolio said:


> I've just tested your modified code and all works as it should. You could try changing the column number to its letter as follows:-
> 
> 
> ```
> ...


I changed the columns as you suggested but am still getting an error.


Something else to note, I had to change the macro name for it to recognize it as a macro. Otherwise it will ask me which macro to run when I click run.


----------



## 6StringJazzer (Jan 1, 2023)

The code that vcoolio gave you should be put in the module for the worksheet containing the data, and it will run automatically whenever there is a change to the data. Do not change the name. You cannot run this macro yourself from the user interface, you must allow Excel to run it automatically.

If you changed code, and then got an error, you should show all of your code and also indicate which line caused the error. It is preferable to paste in your code, rather than using a screenshot. If you paste in actual code we can copy and test it.


----------



## DayTraderKevin (Dec 31, 2022)

Is there a way to do this with VBA - if any cell in B2:B4 = "Buy", then copy column A and paste to column C.

In this example, because B3 = "Buy", I'd want to copy A3 and paste to C3


*A**B**C**1*PriceActionBought Price*2*$1.20*3*$4.87Buy*4*$3.22


----------



## shinigamilight (Jan 1, 2023)

```
Sub job()
        Dim k As Integer
        Dim lr As Long
        lr = Range("A" & Rows.Count).End(xlUp).Row
        
        For k = 2 To lr
                If UCase(Range("B" & k)) = "BUY" Then
                    Range("C" & k) = Range("A" & k)
                End If
        Next k

End Sub
```


----------



## DayTraderKevin (Jan 1, 2023)

6StringJazzer said:


> The code that vcoolio gave you should be put in the module for the worksheet containing the data, and it will run automatically whenever there is a change to the data. Do not change the name. You cannot run this macro yourself from the user interface, you must allow Excel to run it automatically.
> 
> If you changed code, and then got an error, you should show all of your code and also indicate which line caused the error. It is preferable to paste in your code, rather than using a screenshot. If you paste in actual code we can copy and test it.


Thank you for helping a newbie!


----------



## DayTraderKevin (Jan 1, 2023)

shinigamilight said:


> ```
> Sub job()
> Dim k As Integer
> Dim lr As Long
> ...


This is the winner! Thank you!


----------



## DayTraderKevin (Jan 1, 2023)

shinigamilight said:


> ```
> Sub job()
> Dim k As Integer
> Dim lr As Long
> ...


If I wanted to also "SELL" in the same macro, do I need to change the "Dim k" and "Dim lr" to be unique?


----------



## DayTraderKevin (Jan 1, 2023)

shinigamilight said:


> ```
> Sub job()
> Dim k As Integer
> Dim lr As Long
> ...


Now something strange is happening. I modified the code for both buy and sell, and it works, but I'm getting an error that stops the macro.


```
Sub job()
      
        
'Copy and paste buy price
        Dim k As Integer
        Dim lr As Long
        lr = Range("F" & Rows.Count).End(xlUp).Row
        
        For k = 2 To lr
                If UCase(Range("ALZ" & k)) = "BUY" Then
                    Range("AMA" & k) = Range("F" & k)
                End If
        Next k
        
        
'Copy and paste sell price
        Dim b As Integer
        Dim sr As Long
        sr = Range("F" & Rows.Count).End(xlUp).Row
        
        For b = 2 To sr
                If UCase(Range("AME" & b)) = "SELL" Then
                    Range("AMF" & b) = Range("F" & b)
                End If
        Next b
        

End Sub
```


----------



## shinigamilight (Jan 2, 2023)

```
Sub woke()
               
        Dim k As Integer
        Dim lr As Long
       
        lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlWhole, xlByRows, xlPrevious, False).Row
       
        For k = 2 To lr
                If UCase(Range("ALZ" & k)) = "BUY" Then
                    Range("AMA" & k) = Range("F" & k)
                ElseIf UCase(Range("AME" & k)) = "SELL" Then
                    Range("AMF" & k) = Range("F" & k)
                End If
        Next k
       
End Sub
```


----------

