Copy corresponding cell if value is >0

[Kashyap]

Hi,

How can I copy corresponding cell if value in Col E >0..? Something like
below.

Col D |Col E
ABC |12
MNO |2
HTR |0
UTV |0
HNC |5
XYZ |6
QTR |2
GTR |0
MTR |8


Result:

Col D |Col E
ABC |12
MNO |2
HNC |5
XYZ |6
QTR |2
MTR |8

 

[Shane Devenshire]

Hi,

You could apply an AutoFilter and filter on not equal to 0, then copy the
results to a new location

 

[Kashyap]

Hi Shane,

I cannot use auto filter there as the whole report is automated.

Can you suggest me any other way of doing this?

Thanks

 

[Fred Smith]

What does an automated report have to do with it? If autofilter affects the
report, simply turn it off after you have done the copy.

Regards,
Fred.

 

[Chris Bode via OfficeKB.com]

1.Right click on toolbar>select control box
2.from the control box that appears on the sheet1, select and draw a command
button on your sheet
3.Double click the command button to open the code window and paste following
codes
#
Private Sub CommandButton1_Click()
Dim row As Integer, col As Integer
row = 1
col = 5

Dim rowinsheet2 As Integer, colinsheet2 As Integer
rowinsheet2 = 1
colinsheet2 = 5
While Sheet1.Cells(row, col).Value <> ""
If CInt(Sheet1.Cells(row, col).Value) > 0 Then
Sheet2.Cells(rowinsheet2, colinsheet2).Value = Sheet1.Cells(row,
col).Value
Sheet2.Cells(rowinsheet2, colinsheet2 - 1).Value = Sheet1.Cells
(row, col - 1).Value
rowinsheet2 = rowinsheet2 + 1
End If
row = row + 1
Wend
End Sub
#

Note that it will move data from sheet1 to sheet2


Hope this works


Have a nice time

Chris