I have perused quite a few of the auto sort items addressed in the forum with very limited success. So, rather than continue to beat my head against the wall, I'm going to ask for some help from the pro's!
First, I am running Excel2003 on an XP machine.
Second, the set up is a workbook called 'Daily Overview' with two sheets, one named 'Active', which is the sheet of interest, and one named 'Archives'.
The first row is header information, such as FileNum, Date, etc.
Each row thereafter has the pertinent data for each file, which is linked data from other workbooks. The data on this page changes only as the data from the workbooks changes. Thus, the usual approaches to auto sorting seem to fail, since they require hard input for the code to kick in.
I've tried various codes in the worksheet, workbook and modules. I've created a macro that represents the sort I run manually:
One of the code's I've tried, for example, includes:
Can someone kindly give me some guidance on how to make this work? I'll gladly answer any questions you may need, but please be specific. Also be specific as to where code should be placed (i.e. sheet code, this workbook, or module).
ALSO: ONE POINT THAT IS PROBABLY IMPORTANT - The only columns required to have cell changes for the sort are those in any or all of B, C, and E.
Many thanks in advance.![Unsure :unsure: :unsure:](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
First, I am running Excel2003 on an XP machine.
Second, the set up is a workbook called 'Daily Overview' with two sheets, one named 'Active', which is the sheet of interest, and one named 'Archives'.
The first row is header information, such as FileNum, Date, etc.
Each row thereafter has the pertinent data for each file, which is linked data from other workbooks. The data on this page changes only as the data from the workbooks changes. Thus, the usual approaches to auto sorting seem to fail, since they require hard input for the code to kick in.
I've tried various codes in the worksheet, workbook and modules. I've created a macro that represents the sort I run manually:
Code:
Sub AutoSort()
'
' AutoSort Macro
' Macro to auto-sort Daily Overview
'
' Keyboard Shortcut: Ctrl+s
'
Range("A1:Q1813").Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:= _
Range("E2"), Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending _
, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers _
, DataOption3:=xlSortNormal
End Sub
One of the code's I've tried, for example, includes:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'autosort
Dim rng As Range
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' If Intersect(Target, rng) Is Nothing Then Exit Sub
' Set the target Range
Set rng = Range("B2:E100")
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
ActiveSheet.UsedRange.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:= _
Range("E2"), Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending _
, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers _
, DataOption3:=xlSortNormal
End Sub
Can someone kindly give me some guidance on how to make this work? I'll gladly answer any questions you may need, but please be specific. Also be specific as to where code should be placed (i.e. sheet code, this workbook, or module).
ALSO: ONE POINT THAT IS PROBABLY IMPORTANT - The only columns required to have cell changes for the sort are those in any or all of B, C, and E.
Many thanks in advance.