Attempting to perform two different sorts on one worksheet.

[Casey]

Thanks AlfD and Myrna,
AlfD's is a workable solution, but Myrna's idea of using th
BeforePrint seems great, if someone has an idea for AfterPrint? Woul
it be possible to use the CALL function to make the first routine run
after the sheet has printed? Any way to tell when a sheet is finishe
printing

 

[Myrna Larson]

You don't have to wait until the paper is out of the printer, just until Excel
has "fed" the information to Windows and the printer driver. That should take
but a few seconds at most.

Since the 1st sort is part of a Worksheet_Change event macro, the quick way is
to, in the Before Print macro, do the sort, then set up an OnTime macro that
runs a few seconds later and makes a change in the worksheet (i.e. a one-liner
like Cells(1).Formula = Cells(1).Formula). The latter will trigger the Change
macro and a re-sort.

Since you now want to call the 1st sort routine from more than one place, I
(just my personal opinion here) would move that code to its own procedure in a
standard module. Say you call it DoSort1. Then modify your Change macro to
just call DoSort1. And your OnTime macro could also call DoSort1 directly
rather than through the kludge of "modifying" the worksheet.