Searching a range and copying values into a new range.... **Please help** :(

[Lancia]

Hi all.

Can someone help me with a problem I have. Working in Excel, I have
range (B12:E15) that looks like:

Date Consumption Limit Excess
01/01/04 1100 1000 100
02/01/04 980 1000 0
03/01/04 760 1000 0
04/01/04 1200 1000 200

What I need is code that will search through the column calle
'Excess', and for any values that are greater than 0, copy the entir
row (values only, not formatting, or equations that may be behind th
scenes) into a new range starting at G12. The code will search each ro
of the data and for any other rows that have an 'excess', VB will past
it immediately beneath any existing entires in the new range. What
want to be left with is:

Date Consumption Limit Excess
01/01/04 1100 1000 100
04/01/04 1200 1000 200

I hope that anyone can help with this as im brand new to VB. I've bee
searching many forums for people asking similar questions, but i'
having a hard time of it.

Thank

 

[Mike Fogleman]

Lancia, try this for starters:

Sub CopyExcess()
Range("B12").CurrentRegion.Select
Selection.AutoFilter Field:=4, Criteria1:=">0"
Selection.Copy
Range("G12").PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Selection.AutoFilter
Columns("G:G").NumberFormat = "m/d/yyyy"
End Sub

Mike F