copy paste with conditions

[handrianomanana]

Hi there,

I'm new with VBA and walk through old post but can work it out myself, I need more practices.

I want to copy values from one sheet to another if condition is met.

I have a list on one column on sheet1 and another list on other column in sheet2(they might not be the same), anytime the value on sheet2 equals one of the list value on sheet1, then a corresponding cell value on sheet1 should be paste on another cell in sheet2 of the same row.

thanks in advance

 

[turnup4thebooks]

Hi handrianomanana,

I'm new here too. Perhaps try something like this:-

For x=1 to y
if worksheets(1).cells(x,1)=worksheets(2).cells(x,1) Then
worksheets(2).cells(x,2)=worksheets(1).cells(x,1)
end if
next y

 

[handrianomanana]

I used this and get incompatible type

Sub Copy_value()
Dim d
d = ActiveSheet.UsedRange.Rows
For Each d In Worksheets("Feuil2").Rows
If Worksheets("Feuil2").Cells(d, 5) = Worksheets("Feuil1").Cells(d, 2) Then
Worksheets("Feuil1").Cells(d, 17).Copy = Worksheets("Feuil2").Cells(d, 12).Paste
End If
Next d
End Sub

 

[turnup4thebooks]

I was trying to 'paste' some stuff yesterday and kept getting error messages. vba didn't like 'paste' on it's own as a method. When I used 'PasteSpecial' it seemed to do the trick.

 

[turnup4thebooks]

Another couple of things to consider:-

1. How many rows have you got? If you've declared d as an integer and you've got over 32,000 rows then you may have to re-declare d as long.

2. Have you checked that the column of data in Feuil1 is formatted in the same way as the data in Feuil2?

 

[handrianomanana]

Hi, I didn't get any feedback so I worked out this code myself but it doesn't work

Sub copy_withconditions()
'
'Dim dernLigne, i,j
dernLigne = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Count - 1
Application.ScreenUpdating = False
For i = dernLigne To 1 Step -1
If Worksheets(2).Range("E" & i) = Worksheets(1).Range("B" & j) Then
Worksheets(1).Range("Q" & j).Copy Destination:=Worksheets(2).Range("L" & i)
End If
Next i
End Sub