Attempting to perform two different sorts on one worksheet.

[Casey]

Great Gurus of VBA,
I have a worksheet, which uses the following VBA procedure to sort th
data when the last bit of entry occurs in column H. However, when
want to print this spreadsheet I need the data to be sorted differentl
and I am apprehensive about putting the code for the second sort int
the sheet object because I am unsure about how the two might conflic
with each other. Can someone give me an idea about how to have thes
two different sorting routines work on the same sheet and not mess eac
other up. I thought of putting a command button on the sheet to initiat
the second sort but.....????

*First Sort Routine*
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

'This first line looks for a change in column H.
'This first line was a generous gift from Dianne Butterworth
If Not Intersect(Target, Range("H:H")) Is Nothing Then

'This line unprotects the specific worksheet using a password
ActiveWorkbook.Sheets("Roof Beam Take-off").Unprotec
("geekk")

'This line (on four lines to make it readable) sets the range to sort
'the upperleft starting point of the sort, and the various sor
criteria

Range("A3:S152").Sort Key1:=Range("A3")
Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers

'This line protects the sheet with a password and sets the criteria
Only works
'for Excel XP.
ActiveWorkbook.Sheets("Roof Beam Take-off").Protect ("geekk")
_
DrawingObjects:=False, Contents:=True, Scenarios:=True
ActiveWorkbook.Sheets("Roof Beam Take-off").EnableSelection
xlUnlockedCells
End If
End Sub

*Second Sort Routine (the one I want to print)*

Sub RoofBeamSort_For_Report()
'
' RoofBeamSort_For_Report Macro
' Macro recorded 9/27/2004 by Casey Wilkins
'

'
Range("A3:S152").Select
Selection.Sort Key1:=Range("C3"), Order1:=xlAscending
Key2:=Range("Q3") _
, Order2:=xlAscending, Key3:=Range("P3"), Order3:=xlAscending
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
DataOption3:= _
xlSortNormal
End Su

 

[Myrna Larson]

You could add the code for the 2nd sort to the BeforePrint event macro.
Problem is, there's no "AfterPrint" event to allow you to quickly change it
back after printing.

 

[AlfD]

Hi!

Probably the main "watchpoint" on sorts is always be able to roll bac
to where you were.

With that in mind, a couple of suggestions:

The routines are unlikely to conflict, but running one can produce
situation the next wasn't expecting and then maybe trouble. Stay safe?

I would copy the data sheet before sorting it for printing. Either sor
and print the copy, or treat the copy as backup to replace the shee
after printing.

Al