VBA code to copy / paste values only + auto change cell value

[kreso]

Hi to everybody.

I am using Excel 2013, and I have table with two sheets.
In first one called "data" are my data information, and the second one is the one that is troubling me.

In B column, i have "drop down list" that takes list from "data" sheet.
In C and D columns, i have VLOOKUP function that also takes values from "data" sheet, and is in relation with column B.

I copy C i D columns to E and F, and then hide C and D, and after that i made some VBA code that copies values from C & D to E and F (because I must have values only, not function or formula).

The code goes like this:


Sub copy_paste_values()
'
' copy_paste_values Makronaredba
'

'
Columns("C").Select
Selection.Copy
Range("E1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
End Sub

What i want to do is that when I change something in column B, that it automatically change values in E and F columns.
If it can be done without even adding columns E and F, it would be even better.

I hope You understand me, and thanks in advance.

[kreso]

I was able to do what I wanted in this way:

Private Sub Worksheet_Change(ByVal Target As Range)
   '// Runs when a value in B1 to B100 is changed
    If Not Intersect(Range("B1:B100"), Target) Is Nothing Then
        Range("E1:E100").Value = Range("C1:C100").Value
        Range("F1:F100").Value = Range("D1:100").Value
    End If
End Sub

The only thing I am interested in is how to put whole columns instead of a range (1:100).

Thanks in advance.

Bye

[SamT]

Dim LR As Long
Application.EnableEvents = False
Sheet.Calculate

LR = Cells(Rows.Count, "B").End(xlUp).Row

If not intersect(Range("B1:B" & LR) . . .
Range("E1:E" & LR) = Range("C1:C" & LR)
.
.
.
Application.EnableEvents = True

[kreso]

I have a big problem with this vba.
The thing is that when i manually add some different value (not the one that is auto generate with VLOOKUP function) in some E cell (for example E8), when i pick another choice from drop down list in B column (B9), vba code automatically change my manually entered value to value that is pulled from VLOOKUP.

Is there a way to make vba copy/paste values only for row that is in that moment "active", because now it every time when i make change it copy/paste whole range?

I uploaded file, so that you can better understand me.

Thanks in advance

[snb]

Het zou beter zijn als je de bedoeling van dit alles zou beschrijven.
Kan zonder taalproblemen op helpmij.nl

[SamT]

You edited the code in post #1.

Please start over with your question in another thread.

I closed this thread.