Instead of using the Data | Subtotals routine, for which your data
must be sorted beforehand, and then copying the values to another
sheet, you can set up some SUMIF formulae in the second sheet to give
you the same as the subtotals you currently have. Just list the values
of the field that you want the subtotal for in column A, and then a
formula like:

=SUMIF(Sheet1!ref_col,A1,Sheet1!G:G)

and:

=SUMIF(Sheet1!ref_col,A1,Sheet1!Q:Q)

will give you the equivalent, where ref_col will be the column you
generate the subtotals from, eg C:C. Copy these down as required.

Hope this helps.

Pete

On Jul 14, 10:43*am, AndyV wrote:
I have a spreadsheet with roughly 2000 rows and 18 columns. As part of a
macro, I sub total columns 7 and 17. I use a macro to copy select cells into
a second sheet.

After this, I remove the subtotals. I use lookups from the second sheet into
the first sheet. As part of my tests, I have since copied and pasted the
lookup cells as absolute values. There are no longer any lookups in any sheet.

The initial subtotal and it's removal, when creating the macro, took seconds
to complete, however on subsequent attempts, they take hours.

I have since copied and pasted the sheet into a different workbook, and the
subtotalling function in the second workbook takes seconds to complete.

What can I do to improve the performance?