Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for copying specific rows from various worksheets to summary | Excel Discussion (Misc queries) | |||
Macro to delete rows containing specific data | New Users to Excel | |||
Copying Row Data to Specific Worksheet | Links and Linking in Excel | |||
Specific keywords copying data to cells | Excel Worksheet Functions | |||
copying specific data from 1 wrksht to another | Excel Worksheet Functions |