VBA Excel - Ordering data on a set column on 4 sheets in a single workbook

I am fairly new to VBA, and I am really struggling with getting this program to work on more than just one sheet. This program is supposed to order dates in descending order from N14 to N66. The program works as intended on the first sheet but not on the other 3 sheets. The specified area is the same on all 4 sheets (N14 - N66) and the data it needs to arrange is dates on all four sheets.

I have attempted to place the code for each individual sheet, this however had the same results. I have also tried looping it for sheets 2-4, but this did not work either. I have also tried writing the code in different ways, but this was unsuccessful too. I have attempted every fix I could find; however, I was not able to get it to work on all four sheets. I have also tried looking at a very similar problem (VBA-Excel macro only works on current sheet) but I kept getting a "Compile Error: Invalid or unqualified reference" error.

Currently the main program is situated in a Module as a subroutine called "OrderDates" (program below). This subroutine is called for each sheet (in "Microsoft Excel Objects Folder"). Using this structure, the program works perfectly and as intended on the 1 sheet only, but again, does not work on the other 3 sheets, despite the fact that it has to perform the same task in as in the first sheet.

The "OrderDates" subroutine (this is in a module).

Sub OrderDates()
    On Error Resume Next
    If Not Intersect(Target, Range("N:N")) Is Nothing Then
        Range("N14").Sort key1:=Range("N66"), _
        Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=True, _
        Orientation:=xlTopToBottom
    End If
End Sub

Calling the subroutine when a change is made to the worksheet (this is called for each sheet separately in the Microsoft excel Objects folder).

Private Sub Worksheet_Change(ByVal Target As Range)
    Call OrderDates
End Sub

Thank you very much for any responses in advance.

CodePudding user response：

Please, try the next adapted solution. Not answering my clarification question, the solution assumes that the four sheets in discussion contain a similare Event code and you want the called Sub processing the respective triggered event sheet. The called common Sub must have a parameter to know where from the call came:

Sub OrderDates(Target as Range)
    dim ws as Worksheet
    set ws = Target.parent
    If Not Intersect(Target, ws.Range("N:N")) Is Nothing Then
        ws.Range("N14").Sort key1:=ws.Range("N66"), _
        Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=True, _
        Orientation:=xlTopToBottom
    End If
End Sub

Range("N:N") refers the active sheet and it must be fully qualified.

The Event code should look as:

Private Sub Worksheet_Change(ByVal Target As Range)
  Application.EnableEvents = False
    OrderDates Target
  Application.EnableEvents = True
End Sub

To 'inform' the called sub what range should be processed...