Copying Cells from Sheet2 to sheet1

[Gary]

I am new to Excel and was wondering how to perform the simple move.

I have products on sheet2. About 25 of them in the format of

Product Name1, Price, Quantity
Product Name2, Price, Quantity
........
ProductName25, Price, Quantity

The Product Names are filled in and the price is filled in. What I would
like to do is enter a quantity if I want the product. Once I finish
entering in quantities, when i click on Sheet1, I would like the rows that
have a Quantity to be placed on Sheet 1.

How can I do this?

Thanks,

Gary

 

[Patrick Molloy]

add a standard code module and paste this code:

Sub CopyData()
Sheets("Sheet2").Cells.Clear
Range("A1").AutoFilter Field:=3, Criteria1:="<>"
Range("A1").CurrentRegion.Copy
Sheets("Sheet2").Range("A1").PasteSpecial xlAll
Range("A1:C1").AutoFilter
End Sub

Basically it turns on the auto filter, selects non-blanks
in column 3, copies the visible results to sheet2 then
turns off the autofilter

a little crude but it works! - assumes your table is in
three columns from cell A1 of course

HTH

Patrick Molloy
Microsoft Excel MVP