VBA: Auto sort multiple defined ranges when changing another defined range??

[Juhl123]

Hi,

I'm trying to use VBA to automatically sort multiple defined ranges. However, it doesn't work.

I've re-used code from a previous spreadsheet i made (that worked), however, in this spreadsheet i need to sort multiple ranges. Also these should only change when i change the values in a cell range on another worksheet.

• Therefore, I've defined the ranges i want to sort. It's 5 ranges in total, all in Sheet2. They are all 3 columns, but have different number of rows. Lets say I've named them:

1. range1
2. range2
3. range3
4. range4
5. range5

• I want these sorted descending based on column 2 from each range. For this I've defined a cell range for each of my previous 5 ranges. This is basically just the first / top cell in coloumn 2 of each range, and I've called it "range1SortRange". So if 'range1' = B2:D10, then 'range1SortRange' = C2

• I want the 5 ranges to auto sort when i make changes to another defined range (but on Sheet1) called "date"

Below is my code. For now, I've only included range 1 (so i still need 2-5) However, it's not working.:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("date")) Is Nothing Then
Range("range1").Sort Key1:=Range("range1SortRange"), _
Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End Sub

Please can someone help me? Also, which tab/sheet should i put the code in? Sheet1 or Sheet2 as Im using both? I've tried both already.
Thank you in advance.

Kind regards,
Rasmus

 

[Juhl123]

Anyone?