I have two spreadsheets in a workbook as follows:

Source Worksheet contains by customer each product it purchases and the

sales for each product (see sample below). Not all customers buy each

product. There are also new customers added/deleted each month and new

products added/deleted each month.

Column A Column B Column C

January February.....

Customer A Product Code 1 Customer A Product Code 1

100

Customer A Product Code 2

200

Customer A Product Code 3

300

Customer B Product Code 1

500

Customer B Product Code 3

600

Customer B Product Code 4

250

Customer C Product Code 2

350

I have added a column (C) in the above sheet that combines column A & B

(=A1&" "&B1) - I need this for what I need to do below (at least that is

what I came up with).

And, what I want to do in the 2nd sheet of the spreadsheet is list by each

customer in a column and then in the correpsponding row show the total sales

for each product the customer purchases. So Column A will contain a list of

all customers. Row 1 will consist of all the product codes (see sample

below).

The first empty cell in row 2 (so Customer A's purchases) will add up all of

those purchases. Column B will have a formula that adds the total sales of

Product Code 1 etc.....

I'm OK with getting the customer names & product codes to the 2nd sheet

(deleting duplicates etc) where I'm having a problem, because the number of

products and customers will change each month is trying to create a macro

that will copy the formula the correct number of times and changing the

formulas - it can be a straight copy because of absolute references in

formula.. Right now what I have done is create vlookups. So for example in

cell B2 (in the 2nd sheet) I have a formula that reads:

=IF(ISNA(VLOOKUP(A2&" "&$B$1,DATA,2,FALSE)),"",VLOOKUP(A2&"

"&$B$1,DATA,2,FALSE))

Range "data" is in the source sheet and the first column in the range is

Column C.

Second Sheet:

Column A Column B Column C

Product Code 1 Product Code 2 (assume

this is row 1)

Customer A 100 200

Customer B 500

Customer C 350

The formula above works great except I want to set up a macro that enters

the formula in the 2nd sheet in cell B2 and have it copy down and across

equivalent to the number of rows (customers)and number of columns (product

codes). The problem is I can't do a straight copy because the formula in

cell B2 has the B1 as absolute so that when it's copied down column B the

product code remains the same but for Column C the formula is

=IF(ISNA(VLOOKUP(A2&" "&$C$1,DATA,2,FALSE)),"",VLOOKUP(A2&"

"&$C$1,DATA,2,FALSE)). All of this would be OK if I knew the number of

columns I needed to perform the copy and and change the absolute value.

Maybe I have went about this the wrong way??? I'm very new to vba and figure

there must be away of having it count the number of rows in range and

telling it to repeat the same task (move over a cell and replace the

absolute cell reference and then copy)....

I have looked at arrays and crosstabs and tried those but no luck....

Any help would be appreciated....

Much thanks!!!

P.S. Sorry this is so long - I was trying to get as much info as possible to

explain my situation.

## Bookmarks