Macro Help - copying specific data

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

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