How can I work with sub-totaled data without all of the detail. If I do a
sub-total on column 6, collapse the data then sum the column, all of the
detail is used in the calculation. If I try to copy it to a new sheet, or
workbook all of the detail comes along.

The only way I've been able to work around this is to copy the sub-totaled
data to Word and then copy it back to Excel.