Code to copy selective cells from matching rows

[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.

 

[Jacob Skaria]

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

 

[joel]

I think one of your counters is wrogn.

lngLastRow2 = lngLastRow2 + 1


It looks like you are trying to put a space between each row in th
detination which would mean you would need this

lngLastRow2 = lngLastRow2 +

 

[Eddy Stan]

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

 

[Eddy Stan]

Hi Jabob,
Thank you very much.
it helped me lot.
Just i have to put totols for few columns with background yellow color.

Stanley

 

[joel]

Your request was for just values. You can use Copy also to maintain th
formating

This statement copies only the values

TargetSheet.Range("A" & lngLastRow2 + 2) = _
SourceSheet.Range("C" & lngRow).Value

This will copy copy everything
SourceSheet.Range("C" & lngRow).copy _
destination:=TargetSheet.Range("A" & lngLastRow2 + 2)


This will copy values and only formating
SourceSheet.Range("C" & lngRow).copy
TargetSheet.Range("A" & lngLastRow2 + 2).pastespecial _
paste:=xlpastevalues
TargetSheet.Range("A" & lngLastRow2 + 2).pastespecial _
paste:=xlpasteformat