Copy paste cell values based on formula result

[willhans]

I have read similar posts in the forum but can't find a simple solution to my problem. My data is in three columns. Column B has formula which results 'Yes' or " " based on a number in corresponding cell in column A. Each cell in column B is dynamic, using a live api feed and numbers in column A to calculate. When a cell in Column B results Yes, I would like to copy and paste value of that cell into the cell to the right in column C, therefore Macro need to trigger each time when cell value in column B is 'Yes'. I need to do this for 50 sheets in one workbook, data is in the same range across all sheets. Can you please help how I can do this using VBA?

Thank you,

Column A Increment	Column B Formula	Column C Values Yes/No
1.00
0.90
0.80	Yes	Yes
0.70	Yes	Yes

<tbody>
</tbody>

 

[DanteAmor]

Put the following code in the ThisWorkbook events. Then every time a data is updated in column A of any sheet, column C will be updated automatically.

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
  Sh.Range("C3:C" & Sh.Range("B" & Rows.Count).End(xlUp).Row).Value = Sh.Range("B3:B" & Sh.Range("B" & Rows.Count).End(xlUp).Row).Value
End Sub

ThisWorkbook EVENT
- Open the VB Editor (press Alt + F11).
- Over in the Project Explorer, double click on ThisWorkbook.
- In the white panel that then appears, paste the above code.

 

[willhans]

Unfortunately that crashed my spreadsheet and it won't open again. I obviously have back-up so no loss there. My formulas are in column CE and I am looking to paste values in column CF. So I have added the below in ThisWorkbookEvent

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)  Sh.Range("CF3:CF" & Sh.Range("CE" & Rows.Count).End(xlUp).Row).Value = Sh.Range("CE3:CE" & Sh.Range("CE" & Rows.Count).End(xlUp).Row).Value
End Sub

Am I doing something wrong? Thanks.

 

[DanteAmor]

Unfortunately that crashed my spreadsheet and it won't open again. I obviously have back-up so no loss there. My formulas are in column CE and I am looking to paste values in column CF. So I have added the below in ThisWorkbookEvent

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)  Sh.Range("CF3:CF" & Sh.Range("CE" & Rows.Count).End(xlUp).Row).Value = Sh.Range("CE3:CE" & Sh.Range("CE" & Rows.Count).End(xlUp).Row).Value
End Sub

Am I doing something wrong? Thanks.

I'm sorry about that

Try this

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Application.EnableEvents = False
  Sh.Range("C3:C" & Sh.Range("B" & Rows.Count).End(xlUp).Row).Value = Sh.Range("B3:B" & Sh.Range("B" & Rows.Count).End(xlUp).Row).Value
Application.EnableEvents = True
End Sub

 

[willhans]

No worries, excel didn't crash with your udpated code. However I want values in column C to stay after formula result from column B is copied over. Formula result in column B is either "Yes" or " ", when it becomes "Yes" I would like this to be copied into column C and pasted as value. If this "Yes" in column B becomes " " after two minutes, I still want the pasted value in column C to stay as "Yes". Is this doable? Thanks.

 

[DanteAmor]

No worries, excel didn't crash with your udpated code. However I want values in column C to stay after formula result from column B is copied over. Formula result in column B is either "Yes" or " ", when it becomes "Yes" I would like this to be copied into column C and pasted as value. If this "Yes" in column B becomes " " after two minutes, I still want the pasted value in column C to stay as "Yes". Is this doable? Thanks.

Try this

Private Sub Workbook_SheetCalculate(ByVal sh As Object)
    For i = 3 To sh.Range("A" & Rows.Count).End(xlUp).Row
        If sh.Cells(i, "C").Value = "" Or sh.Cells(i, "C").Value = " " Then
            sh.Cells(i, "C").Value = sh.Cells(i, "B")
        End If
    Next
End Sub

 

[willhans]

Thank you very much Dante, hugely appreciated. I just had to add Application.EnableEvents. The code that worked is here

Private Sub Workbook_SheetCalculate(ByVal sh As Object)
Application.EnableEvents = False    
For i = 3 To sh.Range("CD" & Rows.Count).End(xlUp).Row
        If sh.Cells(i, "CF").Value = "" Or sh.Cells(i, "CF").Value = " " Then
            sh.Cells(i, "CF").Value = sh.Cells(i, "CE")
        End If
    Next
Application.EnableEvents = True
End Sub

 

[DanteAmor]

Thank you very much Dante, hugely appreciated. I just had to add Application.EnableEvents. The code that worked is here

Okay, I forgot to put those lines.
I'm glad to help you. Thanks for the feedback.