Thread
:
find and copy to another sheet
View Single Post
#
4
Posted to microsoft.public.excel.misc
Don Guillett
external usenet poster
Posts: 10,124
find and copy to another sheet
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 With Quote
Don Guillett
View Public Profile
Find all posts by Don Guillett