Help with Sort macro based on Data Valiation value

[dbmagnus]

I am new to VB and need help. I’ve searched the forum(s) and can’t quite find the code I need. I'm using Excel 2010. My problem is simple, really. I have a data validation list. When I select a value from the list (cell “B1”) , I have two columns of data with VLOOKUPS that reference the drop-down value (the data columns are range A4:C33). All I want to happen is each time I select a value from the data validation list, I want to sort the range A4:C33 by column B ,descending.

I copied the sort macro as:
[vba]Sub Sort1()
'
' Sort1 Macro
'
Range("A4:C34").Select
ActiveWorkbook.Worksheets("UU Graph").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("UU Graph").Sort.SortFields.Add Key:=Range("B5:B34") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("UU Graph").Sort
.SetRange Range("A4:C34")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("C1").Select
End Sub[/vba]

I know, it's probably crude for a simple sort, but it works. What I haven't had success with is finding code that says when the value of cell B1 changes, to re-sort the range (i.e. run the macro).

Thanks in advance for your help!

[austenr]

Hi welcome to VBAX!! You will want to put that code in the Worksheet_Change event in your VBA code.

Open your editor, select the sheet UUGraph from the upper left window by double clicking it.

Next, in the bigger window to the right, on the left drop down change it to Worksheet. It should automatically change to Worksheet_Change.

Copy and paste your code (minus the Sub and End Sub) in between the existing Sub and End Sub.

Give it a go!!

[dbmagnus]

That works great! But now I'm having a couple issues:

1. I noticed that the order of values in my Data Validation list is incorrect. When I re-sort the list, then make a change in the drop down, the code is error-ing...when I de-bug, it is highlighted on ".Apply" line of the sort code. Why is this happening, and how do I fix?

2. I have a duplicate worksheet on which I want to do the same type of sort when data validatoin value changes. Do I have to re-name the macro? I tried copying it to the appropriate tab in the editor and changed any reference to the first code's worksheet to the new worksheet, but no sorting occurs. Any thoughts?

Thanks!

[dbmagnus]

Can anyone help answer the two questions above? The code I'm using is:

[vba]Private Sub Worksheet_Change(ByVal Target As Range)
Range("B435").Select
With ActiveWorkbook.Worksheets("UU Graph").Sort
.SetRange Range("B435")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("C1").Select
End Sub[/vba]

It works great. But when I copy it to and change the worksheet name from "UU Graph" to "PV Graph", it doesn't work - it's as if it's ignored. How do I get this to work on another worksheet?

THanks!

[austenr]

Did you change the worksheet name in your code to PV Graph?

[dbmagnus]

Yes I did make sure to do that...but get this. I recorded a new macro, which is in Module2, and didn't touch the code in the other two worksheets, and now each sheet's macro works just fine. So that issue appears to be resolved.

Now I just need to figure out how/why when I sort the data in the data validation list the macro breaks at the code's ".Apply" line. Any ideas?