Does it have to be a macro?
You could add a header (Item) to column A (aa, bb, cc column) and then use:
Select your range
Data|pivottable
drag the item to the row field
drag the A header to the data field
drag the B header to the data field
drag the C header to the data field
Each of those should say "sum of...".
If any don't, just doubleclick on it and choose Sum.
Finish up the wizard.
You'll end up with a table that looks like this:
item Data Total
aa Sum of A 0
Sum of B 9
Sum of C 5
bb Sum of A 5
Sum of B 7
Sum of C 4
cc Sum of A 6
Sum of B 8
Sum of C 2
Total Sum of A 11
Total Sum of B 24
Total Sum of C 11
Hit ctrl-a (twice in xl2003) to select all the cells.
edit|copy
followed by: Edit|paste special|Values
Select column B and
edit|replace
what: Sum_of_ (underscore represents a space)
with: (leave blank)
replace all
Then delete those subtotals at the bottom.
Select the top aa (cell A3??) through the bottom of the data (cell A12 in my
sample).
Then hit Edit|goto|special|click blanks.
type = (equal sign)
hit the up arrow
and then hit ctrl-enter
You've just filled the blank cells with their item code.
Now select column A and edit|copy, followed by Edit|paste special|values.
Debra Dalgleish has instructions (with pictures) on how to fill a range at:
http://www.contextures.com/xlDataEntry02.html
