vba code to copy only the values from cells not formulas

February 19, 2015 at 09:52:42
Specs: Windows 7
i have this code to copy the values from a cell in column G in column C. but i have a problem because this code copy the formulas from cells,that i want is to copy only the values not the formulas.is possible to modify the code?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim A As Range, intr As Range, r As Range
Set A = Range("G:G")
Set intr = Intersect(A, Target)
If intr Is Nothing Then Exit Sub

Application.EnableEvents = False
For Each r In intr
If r.Value <> "" Then
r.Copy r.Offset(0, -4)
End If
Next r
Application.EnableEvents = True
End Sub

message edited by zomis


See More: vba code to copy only the values from cells not formulas

Report •

#1
February 19, 2015 at 10:53:49
First, a posting tip:

Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read.

As far as your question, replace this:

r.Copy r.Offset(0, -4)

with this:

r.Copy
r.Offset(0, -4).PasteSpecial xlValues

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 19, 2015 at 12:29:52
DerbyDad03 thank you and you have absolutely right,i didn't notice sorry.continue to have problem,the cells in G is equal to cells in H:(G1=H1,G2=H2...), dont know if it matters,when a cell in H take a value appears in G but not copied in C.what could be wrong?

Report •

#3
February 19, 2015 at 12:45:28
The Worksheet_Change code that you posted contains these lines:

Set A = Range("G:G")
  Set intr = Intersect(A, Target)
    If intr Is Nothing Then Exit Sub

That means that it is checking to see if the changed cell (the Target) is in Column G.

A change in a cell caused by a calculation is not considers a "change" to the WorkSheet_Change event. Therefore, making a manual change to Column H will trigger the code, but the "Target" (which is in Column H) will not intersect with Column G, so the Exit Sub instruction will execute.

That said, you have now confused me. In your first post you said that the code was copying the formula from G to C, now you are saying that it doesn't copy G when a change is made to H.

That second part makes sense for the reason I explained. What is confusing is that I don't what you were doing previously to cause the code the copy the formulas in G to C. The only way for that to happen is to change Column G, and the only way for it to copy formulas is if you entered the formula in Column G, with that entry being "the change" i.e. the Target.

I was originally confused by what you were trying to do with the code in the first place, but I chose to simply answer your question. Now I am really curious as to what you are trying to accomplish.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Related Solutions

#4
February 19, 2015 at 13:40:51
I choose the way to insert manually the values in G,and copied fine in C.thanks anyway!

Report •

Ask Question