Thanks for the reply Tom.

I encounter an error on this line

.Range("BB1").PasteSpecial Paste:=xlPasteAll, _
Transpose:=True

What i want is as this:

A B

1 wbook Wsheet
2
3 Red x
4 Blue x
5 Green x
6 Orange x
7 Gold x

TOTAL: xxx

Please note though that there will be more entries than these colour
so i need the total to be in the next empty cell in column B.

Thanks in advanc

--
Message posted from http://www.ExcelForum.com