Auto Update Sort Options

[philipt]

I have a Page that reads data from multiple pages and then sorts said data. I added a VB script to the sheet so it would sort the data after it pulled it in. The Problem... I have to make a change to that page before it updates my sort. I know I am missing something and I have over complicated something, but here is my script.

Public Sub Worksheet_Change(ByVal Target As Range)
Range("A6").Select
Range(Selection, Selection.End(xlDown)).Select
Range("J6").Select
Range(Selection, Selection.End(xlDown)).Select
Dim erow As Long
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Range("A5:J29").Select
Range("A5:J29").Sort _
Key1:=Range("J6"), _
Order1:=xlAscending, Header:= _
xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
If Cells(erow - 1, 1).Offset(0, 1) = "" Then
Cells(erow - 1, 1).Offset(0, 1).Select
Else
Cells(erow, 1).Select
End If
End Sub

http://philterry.net/new.xlsm

Any help would be greatly appreciated.

 

[James006]

Hi,

Have you tried to use another event type ... such as Worksheet_Calculate()

HTH

 

[philipt]

Hi,

Have you tried to use another event type ... such as Worksheet_Calculate()

HTH

I tried it, but I get a compile error... The procedure declaration does not have a matching description of event or procedure matching the same name. With Public Sub Worksheet_Calculate(ByVal Target As Range) and when I have just Worksheet_Calculate() it gives me a runtime error of 1004. I am not quite sure what else I need to change in my VB script if I use the Calculate option.

 

[philipt]

I think I just figured it out in a much simpler manor than I have seen before. I used this code:

Private Sub Worksheet_Calculate()
Set Target = Sheet3.Range("a5:J29").SpecialCells(xlCellTypeFormulas)
Range("A5:J29").Sort _
Key1:=Range("J6"), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub


Any wholes, or problems foreseen with this?

 

[James006]

Hi,

Target is a reserved word within your code ...

Make sure to declare and use another name for your range

HTH