Copy paste cell values based on formula result

willhans

New Member
Joined
Jul 9, 2019
Messages
4
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.80YesYes
0.70YesYes

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,618
Office Version
  1. 2007
Platform
  1. Windows
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.

Code:
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

New Member
Joined
Jul 9, 2019
Messages
4
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
Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,618
Office Version
  1. 2007
Platform
  1. Windows
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
Code:
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

Code:
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

New Member
Joined
Jul 9, 2019
Messages
4

ADVERTISEMENT

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

Well-known Member
Joined
Dec 3, 2018
Messages
12,618
Office Version
  1. 2007
Platform
  1. Windows
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

Code:
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

New Member
Joined
Jul 9, 2019
Messages
4
Thank you very much Dante, hugely appreciated. I just had to add Application.EnableEvents. The code that worked is here
Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,618
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,342
Messages
5,624,116
Members
416,012
Latest member
rockermom59

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