problem with subtotal

[irato2]

Ive writen an excel spreadsheet which holds a set of unsorted data i
one sheet and the same data sorted in another. whenever someone enter
data in the unsorted sheet it activates a macro which sorts the data i
the other sheet and calculates subtotals on a certain field. M
problem is that when the macro updates the sorted sheet, the gran
total field gets pushed further and further away from the sorted rows
Whenever I add a row of unsorted data the grand total field looks lik
it is pushed down 4 or 5 rows in the sorted data. At the moment, th
sorted data takes up a page, then there is a page of empty rows the th
grand total on the 3rd page. anyone come across this problem before

Attachment filename: sortsample.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=52400

 

[Debra Dalgleish]

At the beginning of your code, clear the sheet with subtotals, and clear
the outline on that sheet.
Instead of copying all of the unsorted sheet, copy the current region:

returncell = ActiveCell.Address

Sheets("auto").Cells.Clear
Sheets("auto").Cells.ClearOutline
ActiveCell.CurrentRegion.Copy