Copying subtotals

[Guest]

I have sorted a data base and created Subtotals, using Data>Subtotals. Then I
collapsed the data so that only the subtotals are visible.

Now I want to create a Lookup table in a different worksheet, displaying
exclusively the Subtotals; I copied the first line of Subtotals from the data
base but, obviously, I cannot drag the formula down because of the hidden
rows in the data table. Is there a way (VBA or otherwise) to copy the
subtotals ONLY into a continuous table, other than the old reliable
point-and-click?

 

[Guest]

So you want to copy the visible cells only... Highlight the area you are
looking to copy and Hit F5 (or Ctrl + G or edit -> GoTo) and then select
Special Cells -> Visible Cells Only. Copy the cells and paste them to the new
sheet...

If you want to you could also change your SubTotals to Pivot Tables.
Generally they are a lot more efficient and flexible. Remove the subtotals
and then select Data -> Pivot Table -> Finish (when the wizard comes up).
Drag the fields onto the table and you are good to go...

 

[Guest]

It should work as long as the cells remain linked to the subtotals. In
retrospect, I don't think that I explained adequately that the data base is
dynamic and that the "pasted" cells (which are going to become part of a
vlookup table) also need to update themselves with every workbook edit.