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

[girapas]

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...

 

[HalfAce]

If you had a formula in A1 that you want the value (not the formula) for in B1, you could just do this:

[B1].Value = [A1].Value

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

Hope it helps.

 

[girapas]

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]

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:

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]

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]

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:

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

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]

The second, with copy - paste special works fine.
Thank you very much

 

[HalfAce]

You're very welcome.

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