I have sorted a data base and created Subtotals, using DataSubtotals. 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?

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...
--
HTH...

Jim Thomlinson


"KG" wrote:

I have sorted a data base and created Subtotals, using DataSubtotals. 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?

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.

"Jim Thomlinson" wrote:

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...
--
HTH...

Jim Thomlinson


"KG" wrote:

I have sorted a data base and created Subtotals, using DataSubtotals. 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?