Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
find and copy to another sheet
Hi
I have a spreadsheet which has two sheets. 'Data' which has a list of parts and their costs week by week. example: column A has title of 'Computer', column B has it's ref number '9997' , column c has it's text ref 'PC' then columns E to BC have the cost of running week by week '£0.87'. There are approx 140 parts each are unique and are sorted by highest cost each week, therefore the same part is not in the same row each week. What i need to do is find a part by identifing the ''part/ref no/text ref'' then returning all of that particular row's info onto sheet 1. I've tried using macros but they only work if the information is in the same row each week. Is there any functions or macros/vb code that will do what i need? Thanks in advance Paul |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
find and copy to another sheet
Post your macro for comments and changes
-- Don Guillett Microsoft MVP Excel SalesAid Software "Paul" wrote in message ... Hi I have a spreadsheet which has two sheets. 'Data' which has a list of parts and their costs week by week. example: column A has title of 'Computer', column B has it's ref number '9997' , column c has it's text ref 'PC' then columns E to BC have the cost of running week by week '£0.87'. There are approx 140 parts each are unique and are sorted by highest cost each week, therefore the same part is not in the same row each week. What i need to do is find a part by identifing the ''part/ref no/text ref'' then returning all of that particular row's info onto sheet 1. I've tried using macros but they only work if the information is in the same row each week. Is there any functions or macros/vb code that will do what i need? Thanks in advance Paul |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
find and copy to another sheet
Here's the macro which dosen't do what i need it to Sheets("data").Select Cells.Find(What:="computer", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate <<<<I Need to expand this part to search for the ref number and text ref to ensure an exact macth Rows("55:55").Select Selection.Copy Sheets("sheet1").Select Rows("27:27").Select Selection.Insert Shift:=xlDown <<<<< this just selects the same row every time instead i need it to copy the entire row that the search finds Thanks Paul Watkins "Don Guillett" wrote in message ... Post your macro for comments and changes -- Don Guillett Microsoft MVP Excel SalesAid Software "Paul" wrote in message ... Hi I have a spreadsheet which has two sheets. 'Data' which has a list of parts and their costs week by week. example: column A has title of 'Computer', column B has it's ref number '9997' , column c has it's text ref 'PC' then columns E to BC have the cost of running week by week '£0.87'. There are approx 140 parts each are unique and are sorted by highest cost each week, therefore the same part is not in the same row each week. What i need to do is find a part by identifing the ''part/ref no/text ref'' then returning all of that particular row's info onto sheet 1. I've tried using macros but they only work if the information is in the same row each week. Is there any functions or macros/vb code that will do what i need? Thanks in advance Paul |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
find and copy to another sheet
Thanks Don
This macro works to a point. the find part needs to be expanded to search for 3 different columns that all match in a row though. e.g computer/9777/pc can this macro be expanded to search for all these at the same time? i did use this vb code originally but could not get it to work to paste the entire row though. Dim vFind As Variant Dim lFirstRow As String Dim rFound As Range vFind = Sheet1.Range("a11:a13").Value ****(A11 to A 13 contain the search parameters)**** With Sheet2.Range("A:A") ****Sheet2 is called 'data'****** Set rFound = .Find(vFind(1, 1), LookIn:=xlValues) If Not rFound Is Nothing Then lFirstRow = rFound.Row Do If rFound.Offset(, 1).Value = vFind(2, 1) And rFound.Offset(, 2).Value = vFind(3, 1) Then Worksheets("Sheet1").Range("A13").Value = ..Cells(rFound.Row, "E") Worksheets("Sheet1").Range("B13").Value = ..Cells(rFound.Row, "F") *****need to replace this to paste the entire row instead***** Exit Sub End If Set rFound = .FindNext(rFound) Loop While Not rFound Is Nothing And rFound.Row lFirstRow End If End With MsgBox "No Data Found" Paul "Don Guillett" wrote in message ... try this (UN tested) with Sheets("data") myrow=cells.Find(What:="computer", After:=.cells(1,1), LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).row .Rows(myrow).Copy Sheets("sheet1").cells(27,1) end with If all else fails, you may send your workbook to my address below along with a snippet of this message and exactly what you want. -- Don Guillett Microsoft MVP Excel SalesAid Software "Paul Watkins" wrote in message ... Here's the macro which dosen't do what i need it to Sheets("data").Select Cells.Find(What:="computer", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate <<<<I Need to expand this part to search for the ref number and text ref to ensure an exact macth Rows("55:55").Select Selection.Copy Sheets("sheet1").Select Rows("27:27").Select Selection.Insert Shift:=xlDown <<<<< this just selects the same row every time instead i need it to copy the entire row that the search finds Thanks Paul Watkins "Don Guillett" wrote in message ... Post your macro for comments and changes -- Don Guillett Microsoft MVP Excel SalesAid Software "Paul" wrote in message ... Hi I have a spreadsheet which has two sheets. 'Data' which has a list of parts and their costs week by week. example: column A has title of 'Computer', column B has it's ref number '9997' , column c has it's text ref 'PC' then columns E to BC have the cost of running week by week '£0.87'. There are approx 140 parts each are unique and are sorted by highest cost each week, therefore the same part is not in the same row each week. What i need to do is find a part by identifing the ''part/ref no/text ref'' then returning all of that particular row's info onto sheet 1. I've tried using macros but they only work if the information is in the same row each week. Is there any functions or macros/vb code that will do what i need? Thanks in advance Paul |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
find and copy to another sheet
Without looking in too much detail since I can't see your layout, I would
v1=range("a11") v2=range("a12") v3=range("a13") then find v1 and use if offset(0,2)=v2 and v3=offset(0,3) to determine the next etc. I repeat my offer -- Don Guillett Microsoft MVP Excel SalesAid Software "Paul Watkins" wrote in message ... Thanks Don This macro works to a point. the find part needs to be expanded to search for 3 different columns that all match in a row though. e.g computer/9777/pc can this macro be expanded to search for all these at the same time? i did use this vb code originally but could not get it to work to paste the entire row though. Dim vFind As Variant Dim lFirstRow As String Dim rFound As Range vFind = Sheet1.Range("a11:a13").Value ****(A11 to A 13 contain the search parameters)**** With Sheet2.Range("A:A") ****Sheet2 is called 'data'****** Set rFound = .Find(vFind(1, 1), LookIn:=xlValues) If Not rFound Is Nothing Then lFirstRow = rFound.Row Do If rFound.Offset(, 1).Value = vFind(2, 1) And rFound.Offset(, 2).Value = vFind(3, 1) Then Worksheets("Sheet1").Range("A13").Value = .Cells(rFound.Row, "E") Worksheets("Sheet1").Range("B13").Value = .Cells(rFound.Row, "F") *****need to replace this to paste the entire row instead***** Exit Sub End If Set rFound = .FindNext(rFound) Loop While Not rFound Is Nothing And rFound.Row lFirstRow End If End With MsgBox "No Data Found" Paul "Don Guillett" wrote in message ... try this (UN tested) with Sheets("data") myrow=cells.Find(What:="computer", After:=.cells(1,1), LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).row .Rows(myrow).Copy Sheets("sheet1").cells(27,1) end with If all else fails, you may send your workbook to my address below along with a snippet of this message and exactly what you want. -- Don Guillett Microsoft MVP Excel SalesAid Software "Paul Watkins" wrote in message ... Here's the macro which dosen't do what i need it to Sheets("data").Select Cells.Find(What:="computer", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate <<<<I Need to expand this part to search for the ref number and text ref to ensure an exact macth Rows("55:55").Select Selection.Copy Sheets("sheet1").Select Rows("27:27").Select Selection.Insert Shift:=xlDown <<<<< this just selects the same row every time instead i need it to copy the entire row that the search finds Thanks Paul Watkins "Don Guillett" wrote in message ... Post your macro for comments and changes -- Don Guillett Microsoft MVP Excel SalesAid Software "Paul" wrote in message ... Hi I have a spreadsheet which has two sheets. 'Data' which has a list of parts and their costs week by week. example: column A has title of 'Computer', column B has it's ref number '9997' , column c has it's text ref 'PC' then columns E to BC have the cost of running week by week '£0.87'. There are approx 140 parts each are unique and are sorted by highest cost each week, therefore the same part is not in the same row each week. What i need to do is find a part by identifing the ''part/ref no/text ref'' then returning all of that particular row's info onto sheet 1. I've tried using macros but they only work if the information is in the same row each week. Is there any functions or macros/vb code that will do what i need? Thanks in advance Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
visual basic. find copy row past into new sheet | Excel Worksheet Functions | |||
Macro to find matching date and copy values to another sheet | Excel Discussion (Misc queries) | |||
'Copy to' Advance Filter depend only on sheet ID not start sheet | Excel Worksheet Functions | |||
Find text in cell, copy row to new sheet | Excel Discussion (Misc queries) | |||
relative sheet references ala sheet(-1)!B11 so I can copy a sheet. | Excel Discussion (Misc queries) |