MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VB script to autosort a range and output to diff range

Posted by Dave LaFrankie on April 12, 2001 8:34 AM

Running Excel 97
I want to collect data from a range of cells A1:A3, sort it and then output it to a new range B1:B3 automatically. I mean when the data in the first
range changes the second range would automatically be changed and resorted. Can this be done? Is there a VB script I can throw in there to do this?.
simple example:
1 oranges apples
2 apples oranges
3 pears pears

I would really appreciate your help.

Posted by Dave Hawley on April 12, 2001 10:52 AM

Hi Dave

Right click on your sheet name tab and select "View Code" and paste in this code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim RWatchRange As Range
'Written by OzGrid Business Applications
On Error GoTo ResetEvents
If Target.Cells.Count > 1 Then Exit Sub
Set RWatchRange = Range("A1:A3")
If Not Intersect(RWatchRange, Target) Is Nothing Then
Application.EnableEvents = False
With RWatchRange
.Sort Key1:=.Cells(1, 1), order1:=xlAscending, Orientation:=xlSortColumns
RWatchRange.Copy Destination:=Range("B1")
End With
End If
Set RWatchRange = Nothing
Application.CutCopyMode = False
Application.EnableEvents = True
End Sub


OzGrid Business Applications

Posted by Dave LaFrankie on April 16, 2001 10:49 AM

Ok ... this works great when the data is actually keyed into the tested cells. But what if the data is referneced from other cells based on a Vlookup?

I mean it seems that for the sort to work I actually hve to go into the tested cells and at least hit F2 and then enter ... then the script does it's job. I was looking for hands free type of thing. I am not sure if you follow what I am saying or not. (hope so)