Copy/paste only cells>0--Celia I have a case simmilar with the one you solved for Mac P.-Please help

Posted by Garcia C. on May 30, 2000 5:38 AM

My case is simmilar with the one below posted by Mac P. and solved by Celia. I tried to adapt that macro to my case but it was in vain. I will appreciate any help.

I'll adapt my case to the one already solved below to make your guys easier to solve, I guess.

In A1, A2, A3, A4, A5 I have Label 1, Label 2 to Label 5.

In B1, B2, B3, B4, B5 I have amounts 5, 0, 3, 7, 0

Base on condition for every B1 to B5 >0:

1-I need to return in C1 and down, info from A1 to A5 but only the ones for which B value > 0; in this case C1 will have Label 1, C2 Label 3, C3 Label 4.

2-I need also to return in D1 and down, info from B1 to B5, only values >0; D1 will be =5, D2=3, D3=7 and that's it. Much appreciated any help.

I tried to work on from your macro Celia using AND with the 2nd range but it does not work.

Posted by Celia on May 30, 2000 3:34 PM

Garcia C.
Try the following :-

Sub Macro1()
Dim colB As Range, cell As Range, dest As Range
Set colB = Range(Range("B1"), Range("B65536").End(xlUp))
Set dest = Range("C1:D1")
Range(Range("C1:D1"), Range("C65536:D65536").End(xlUp)).ClearContents
For Each cell In colB
If cell.Value > 0 Then
cell.Offset(0, -1).Resize(, 2).Copy dest
Set dest = dest.Offset(1, 0)
End If
Next
End Sub

If you want the info in columns C & D to be updated automatically each time there is a change made to column B, then instead of the above put the following into the Sheet module :-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim colB As Range, dest As Range, cell As Range, source As Range
Set colB = Range(Range("B1"), Range("B65536").End(xlUp))
Set dest = Range("C1:D1")
Set source = Intersect(Target, colB)
If Not source Is Nothing Then
Range(Range("C1:D1"), Range("C65536:D65536").End(xlUp)).ClearContents
For Each cell In colB
If cell.Value > 0 Then
cell.Offset(0, -1).Resize(, 2).Copy dest
Set dest = dest.Offset(1, 0)
End If
Next
End If
End Sub

Celia

Posted by Celia on May 30, 2000 4:28 PM

Garcia P.
The second line of the Worksheet_Change procedure should be :-

Set colB = Range(Range("B1"), Range("B65536").End(xlUp).Offset(1, 0))

Celia