Code to copy selective cells from matching rows

  • Thread starter Thread starter Eddy Stan
  • Start date Start date
E

Eddy Stan

Hi

the following code writes the whole row, when the look value is found
if i require from the found row, only value (number, string or date) in cell
of columns c,d,e,j,k,m only has to be brought, then
what should be my code.

For lngRow = 1 To lngLastRow1
' If Format(SourceSheet.Range("A" & lngRow), "ddmmm") = "05Aug" Then
If SourceSheet.Range("E" & lngRow).Value = varFind Then
SourceSheet.Rows(lngRow).Copy TargetSheet.Rows(lngLastRow2 + 2)
lngLastRow2 = lngLastRow2 + 1
End If
Next

thank you all.
 
Try

For lngRow = 1 To lngLastRow1
If SourceSheet.Range("E" & lngRow).Value = varFind Then
SourceSheet.Rows(lngRow).Copy TargetSheet.Rows(lngLastRow2 + 2)
TargetSheet.Range("A" & lngLastRow2 + 2) = SourceSheet.Range("C" &
lngRow).Value
TargetSheet.Range("B" & lngLastRow2 + 2) = SourceSheet.Range("D" &
lngRow).Value
TargetSheet.Range("C" & lngLastRow2 + 2) = SourceSheet.Range("E" &
lngRow).Value
TargetSheet.Range("D" & lngLastRow2 + 2) = SourceSheet.Range("J" &
lngRow).Value
TargetSheet.Range("E" & lngLastRow2 + 2) = SourceSheet.Range("K" &
lngRow).Value
TargetSheet.Range("F" & lngLastRow2 + 2) = SourceSheet.Range("M" &
lngRow).Value
lngLastRow2 = lngLastRow2 + 1
End If
Next
 
Jacob Skaria said:
Try

For lngRow = 1 To lngLastRow1
If SourceSheet.Range("E" & lngRow).Value = varFind Then
SourceSheet.Rows(lngRow).Copy TargetSheet.Rows(lngLastRow2 + 2)
TargetSheet.Range("A" & lngLastRow2 + 2) = SourceSheet.Range("C" &
lngRow).Value
TargetSheet.Range("B" & lngLastRow2 + 2) = SourceSheet.Range("D" &
lngRow).Value
TargetSheet.Range("C" & lngLastRow2 + 2) = SourceSheet.Range("E" &
lngRow).Value
TargetSheet.Range("D" & lngLastRow2 + 2) = SourceSheet.Range("J" &
lngRow).Value
TargetSheet.Range("E" & lngLastRow2 + 2) = SourceSheet.Range("K" &
lngRow).Value
TargetSheet.Range("F" & lngLastRow2 + 2) = SourceSheet.Range("M" &
lngRow).Value
lngLastRow2 = lngLastRow2 + 1
End If
Next
Click to expand...
 
Hi Jabob,
Thank you very much.
it helped me lot.
Just i have to put totols for few columns with background yellow color.

Stanley
 
Back
Top