PDA

View Full Version : [SOLVED] Macro for copy/paste values for only that row where the change is made



kreso
05-22-2016, 11:28 PM
I have excel table that I am using (you can see it in attachment), and i made the two codes that you can see lower.


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True 'Eliminate Edit status due to doubleclick
Target.Offset(1).EntireRow.Insert
Target.EntireRow.Copy Target.Offset(1).EntireRow
On Error Resume Next
Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
On Error GoTo 0
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
'// Runs when a value in B5 to B100 is changed
If Not Intersect(Range("B5:B100"), Target) Is Nothing Then
Range("E5:E100").Value = Range("C5:C100").Value
Range("F5:F100").Value = Range("D5:100").Value
End If
End Sub


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?


Thanks in advance

kreso
05-29-2016, 11:22 PM
Anyone?

mdmackillop
05-30-2016, 01:02 PM
Private Sub Worksheet_Change(ByVal Target As Range)
'// Runs when a value in B5 to B100 is changed
Dim rw As Long
rw = Target.Row
On Error Resume Next
Application.EnableEvents = False 'Prevent code looping
If Not Intersect(Range("B5:B100"), Target) Is Nothing Then
Range("E" & rw).Resize(, 2).Value = Range("C" & rw).Resize(, 2).Value
End If
Exits:
Application.EnableEvents = True
End Sub

kreso
05-31-2016, 04:42 AM
Thank you so much, it works great.