transfering data within an excel workbook

I could get you started assuming this is what you want:

1) cycle through specific sheets
2) look in a column for a specific text
3) if found then add to a variable
4) att text and total to a cover sheet in specific area

is this what you are looking for?

the formula looks like
=sum(sheet2!b4:b10,sheet3!d12:d40,sheet4!a2:a30,...)

If the item's location is unknown, you can use VBA to locate it, then do the sums, but if the quantities to sum are placed systematically, you can just use the sum formula above.