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:
A B
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
'www.ozgrid.com
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
ResetEvents:
Application.CutCopyMode = False
Application.EnableEvents = True
End Sub
Dave
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)
