Hi,

I use Excel 2003. I store my raw data in 2 seperate excel files(name "buy
wk20.xls" and "sell wk20.xls"). This 2 files get updated weekly, so the row
may reduce/increase.

I have a another excel file for management reporting purpose. There are 3
worksheets in this workbook (named "checkbook wk20.xls").

Based on the part numbers that appear on column A on each worksheet in the
checkbook, I need to be able to extract a certain data range from 2 raw files
and paste them to the correct worksheet & correct lines in the checkbook.

--------------------
1. Format on checkbook, "North A" worksheet,
on column a2-a4, I have "Part # ABC1234DEF ", "Buy", "Sell"
on column c2-f2, I have "ww20 ww21 ww22 ww23"
and buy/sell data (format=number) on c3-f4.

and repeat the same header pattern on cell a13, a24, a35, a46, a57, a68 and
continue until there's no more part# on this worksheet.

then on the second worksheet "South B",
the header pattern is still the same, except it starts on different cell
like a5, a16, a27, a38........

then on the third worksheet "East C",
also the same header pattern, but it starts on cell a3, a14, a25,....

2. Format on "buy wk20.xls", there is only 1 worksheet "buy wk20",
column e has the part num e.g. "ABC1234DEF", column g to j has the raw data.

3. Format on "sell wk20.xls", has 1 worksheet "sell wk20",
column a has the part num e.g. "ABC1234DEF", column b to e has the raw data.

----------------------

So:
I want to look for part num one by one in the checkbook on every worksheets,
match it to buy and sell files, copy the raw data on the same line and paste
them back to checkbook.

Buy data will come from column g-j from buy.xls and paste to column c3-f3 on
checkbook.xls

Sell data will come from column b-e from sell.xls and it will be pasted to
column c4-f4 on checkbook.xls.

if part number on checkbook.xls is found on a13, then buy data will go to
c14-f14, sell data will go to c15-f15.

Tricky part is that the part num is in the same cell with its header and the
space before and after part num is inconsistent.
there could be 1 or more spaces before and after part num.
We need to remove not just the space in front and at the back, but also the
"Part #" header, so that we can match it to the buy/sell file.

I can't do this using normal macro recording. Looks like complex
programming is required. Hope someone can help.

Regards,
choo