Make a column that automatically pastes the values from another column

Jyggalag

Active Member
Joined
Mar 8, 2021
Messages
445
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi all,

I currently have this setup:

1667216847263.png


I would like to have column E automatically paste column D, but as values. Right now column D is filled with the formula (=A+B). I would like to have this pasted as values into column E, but done so on an automatic basis.

The easiest solution I see would be to make a macro that pastes column D into column E.

However, I have seen some VBA codes that can do fancy tricks that might workaround this. For example, I remember seeing one that would turn a column into a subtraction/addition column, so if you inserted a number into this column, it would automatically subtract it or add it to the number in the next column.

Is it possible to do something similar to this, through a code for example? Where it sets column E to automatically paste the values from D into itself, preferably from cell E2 and downwards.

Would be amazing. Thank you everybody! :)

Kind regards,
Jyggalag


EDIT:

I essentially want Excel to run this VBA code, but at all times without me pressing on a macro:

VBA Code:
Sub TestSumIf()
Range("E2:E8").Value = Range("D2:D8").Value
End Sub
 
Last edited by a moderator:

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Put this code inthe worksheet code for the sheet where you want it. It is trigger by the recalculate event which means it runs =everytime D is recalculated.
VBA Code:
Private Sub Worksheet_Calculate()
lastrow = Cells(Rows.Count, "D").End(xlUp).Row
Range("d2:d" & lastrow).Copy Range("e2:e" & lastrow)
End Sub
 
Upvote 0
sorry I forgot to put the .values:
try this one:
VBA Code:
Private Sub Worksheet_Calculate()
lastrow = Cells(Rows.Count, "D").End(xlUp).Row
inarr = Range("d2:d" & lastrow).Value
Range("e2:e" & lastrow) = inarr
End Sub
 
Upvote 0
Solution
sorry I forgot to put the .values:
try this one:
VBA Code:
Private Sub Worksheet_Calculate()
lastrow = Cells(Rows.Count, "D").End(xlUp).Row
inarr = Range("d2:d" & lastrow).Value
Range("e2:e" & lastrow) = inarr
End Sub
This is amazing! Excellent code, thank you so much my friend! You've saved me a lot of trouble :D

Have a lovely day! :-)
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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
Back
Top