(e-mail address removed) wrote...
....
Is there a way to sort all the entries in the workbook by, say, Column
E in all the 10 worksheets and either dump the resulting sorted data
in the existing worksheets or in new worksheets in the same/new
workbook?
....
Yes. It's cumbersome to do manually. If you have a single column as
sort key, you'd need to add one extra column to the tables in each
worksheet. If you have multiple columns as sort keys, you'd need to
add two extra columns to the tables in each worksheet. For now I'll
assume a single column sort key, and that there are no duplicate sort
key values.
First enter the names of these worksheets in a single column range in
a different worksheet (not one of the ones to be sorted - not strictly
necessary, but easier). Name that range WSLST. Then select all these
worksheets.
If the single column sort key were column A, and if row 1 were
headings in each worksheet, then move to row 2 in the first empty
column to the right of the table to be sorted. Hold down [Shift] and
[Ctrl] keys and press the [Down] arrow key. Type the formula
=INT(SUMPRODUCT(COUNTIF(INDIRECT("'"&WSLST&"'!A2:A65536"),
"<"&A2))/65535)
hold down the [Ctrl] key and press [Enter]. This should enter this
formula into every cell from row 2 to row 65536 in this column. The
formula returns the group into which that row's record falls, so the
result worksheet into which it should be placed.
At this point it's easier to create new worksheets with the sorted
tables. With the worksheets still grouped, run the menu command Insert
Worksheet. In Excel 2003 at least this inserts a number of new new
worksheets equal to the number of selected worksheets, and ungroups
worksheets so only one of the new worksheets is selected.
Next, cycle through each of the originally selected worksheets singly
in turn. In each, select the augmented table - the original table plus
the column of formulas - and autofilter (Data > Filter > Autofilter).
Filter on the column of formulas, selecting 0 through 9 (or one less
than the number of worksheets named in WSLST).
In the first worksheet you process, copy the filtered table including
row 1 but not including the column of formulas, and paste into the new
worksheet corresponding to that group beginning in row 1. This will
put the headers into row 1 in the new worksheets.
In the subsequent worksheets you process, don't include row 1 or the
column of formulas when you select the filtered records to copy, and
paste into the corresponding new worksheet just below the previously
pasted records. When you're finished copying records, go through each
of the new workbooks and sort them each (separately) on column A.
This could be wrapped into a macro.
