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


"Eddy Stan" wrote:

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.

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 the
detination which would mean you would need this

lngLastRow2 = lngLastRow2 + 2


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=170347

Microsoft Office Help

"Jacob Skaria" wrote:

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


"Eddy Stan" wrote:

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.

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

Stanley


"Jacob Skaria" wrote:

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


"Eddy Stan" wrote:

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.

Your request was for just values. You can use Copy also to maintain the
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:=xlpasteformats


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=170347

Microsoft Office Help