ExcelBanter - Macro Help - copying specific data

Dave,
Hope this gets you started:
Sub test()
Call CopyData("Paint", 2)
End Sub

Sub CopyData(FindValue, OutRow)
'
' FindValue = Product
' Outrow= Next row in output sheet
'
Dim inWs As Worksheet
Dim outWs As Worksheet
Dim rng1 As Range
Dim Firstrow As Long, Lastrow As Long, i As Integer

Set inWs = Worksheets("Sheet1")
Set outWs = Worksheets("Sheet2")
inWs.Select
' Set range to search i.e. column with product (A)
Set rng1 = inWs.Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
' Find first row with required product
Firstrow = rng1.Find(FindValue, LookIn:=xlValues).row
' Determine last row by counting number of rows with this product
Lastrow = Firstrow + Application.CountIf(rng1, FindValue) - 1
' Copy data
For i = Firstrow To Lastrow
inWs.Cells(i, "A").Copy outWs.Cells(OutRow, "A")
inWs.Cells(i, "B").Copy outWs.Cells(OutRow, "I")
OutRow = OutRow + 1
Next i

End Sub

HTH

"Dave" wrote:

I have a data source with several different columns of data. There are many
rows wtihin the source. I would like to sort the data and then have the
Macro copy certain information out of the file and paste it into another.

Example: copy product name and type

A B
1 Product Desc
2 Hammer 20 oz
3 Hammer Sledge
4 Hammer 16oz
5 Nails 10p
6 Paint Red
7 Paint Green
8 Paint Blue
9 Paint Yellow
10 Screwdriver Phillips

When the information is pasted into the new file, the columns do not match
the original file.

A B C D E F G H I
1 Product Desc 2 Paint
Red 3 Paint Green
4 Paint Blue 5 Paint
Yellow

Is it possible to do this with a macro? If not, is there a formula/series
of formula that would do this?

Thanks