0

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.

Ganz Hunslet G2
  • 3
  • 2
  • So, you have such `Change` events code in the four sheets you want processing. Is this understanding correct? If so, you need to process the sheet where the event calling the sub exists. Is this a correct understanding, too? – FaneDuru Nov 10 '22 at 10:42
  • Yes, that is correct. I call the subroutine in all four sheets. Thank you for the pointer, I'll try processing the sheet when the subroutine is called. – Ganz Hunslet G2 Nov 10 '22 at 11:08
  • Then, did you try the solution I proposed in my answer? – FaneDuru Nov 10 '22 at 11:31

1 Answers1

1

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...

FaneDuru
  • 38,298
  • 4
  • 19
  • 27
  • Thank you, this would work but I keep getting a Run-time error 1004. According to the error message, all merged cells need to be the same size. I oddly only encounter this error when I apply the event code to a sheet other than the initial one despite that all 4 sheets should (in theory) be the same. – Ganz Hunslet G2 Nov 10 '22 at 12:25
  • @Ganz Hunslet G2 On which code line is the error raised? Does the code work on the other three worksheets? Which range area has merged cells? Merged between columns or between rows? – FaneDuru Nov 10 '22 at 12:28
  • The debugger highlights rows 5 to 8. The code works on worksheet 1 and generates an error for worksheets 2-4. The merged cells exist between A12/13 and AA12/13 and these are merged rows. Additionally, there is also a merged row on 2H/I, where H and I have been merged into a single cell. However, none of these merges affect column N. – Ganz Hunslet G2 Nov 10 '22 at 13:09
  • Is it a problem to unmerge the cells in discussion? It can be done in code. If not, ai am afraid Excel will not sort it. Look [here](https://learn.microsoft.com/en-us/office/troubleshoot/excel/error-sort-a-range-merged-cell) to better understand what is it about and what can be done... – FaneDuru Nov 10 '22 at 13:16
  • 1
    Thank you for your time, I've managed to get it working now. The problem was with the other 3 sheets and not with your program. Thank you again. – Ganz Hunslet G2 Nov 10 '22 at 16:37