Code to copy only values from a cell with formula to another cell

girapas

Board Regular
Joined
Apr 20, 2004
Messages
150
I have a sheet in which cells at columns A to F change through a formula.
What is the code for the "Copy - Paste Special - Values (only, but not the formula)" when any value of cells A to F change?
Thanks in advance...
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
If you had a formula in A1 that you want the value (not the formula) for in B1, you could just do this:
Code:
[B1].Value = [A1].Value

No need to copy or paste, just making the values equal.

Hope it helps.
 

girapas

Board Regular
Joined
Apr 20, 2004
Messages
150
Thank you for the reply. It works fine for a single cell, but I can't manage it with multiple cells in a column. I get the compile error "Invalid qualifier" for [cell2]
I've put the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Byte
Dim cell1, cell2 As String

For i = 1 To 100
cell1 = "A" & i
cell2 = "B" & i
If Target.Address = cell1 Then [cell2].Value = [cell1].Value
Next i

End Sub
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
Hi,
It looks to me like your code (if it worked as written) would simply be testing to see if the change happened within the range A1:A100, and if so, make the cell in column B equal to the cell that got changed in column A. (yes?)

If that's correct then you don't need to assign variables and loop through the range, we can do it with just this one liner:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then _
   Target(, 2).Value = Target.Value
End Sub

If this is not your intent then can you post back with what is?
Oh, and one question, are the values in column A getting changed manually, or are they formulas that are updating?
 

girapas

Board Regular
Joined
Apr 20, 2004
Messages
150

ADVERTISEMENT

Yes, it's correct. I want to do what you've written, but the code you gave me doesn't work.
Notice that each cell in column A changes by a formula. For that reason I asked for the Copy - Paste Special - Values (only) code.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
I suspected that might be the case.
We should then use the worksheet calculation event instead of the change event.
If you were to (for some reason) want to loop through A1:A100, you could do it like this:
Code:
Private Sub Worksheet_Calculate()
Dim i As Long
For i = 1 To 100
  Cells(i, "B").Value = Cells(i, "A").Value
Next i
End Sub
But... since this is not determining which formula return has changed with the update, we can simply copy A1:A100 & paste as values to B1:B100. (like so):
Code:
Private Sub Worksheet_Calculate()
Range("A1:A100").Copy
Range("B1:B100").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub
Does either one of these work for what you want?
 

girapas

Board Regular
Joined
Apr 20, 2004
Messages
150
The second, with copy - paste special works fine.
Thank you very much
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
You're very welcome.

(And actually, they both work great but the second one will be [marginally] faster.) ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,861
Messages
5,574,708
Members
412,613
Latest member
EFRATA
Top