Sort Dynamic Table with Condition

[zinah]

Hi,

I have the below table, what I need is to sort the numbering by ascending order, it's easy if I have a fixed table, but the challenge is this table is dynamic, it changes whenever I change the EE ID. Can you help me with the formula that solve this issue?

EE ID	AAAA
KEY_EEID	Goal#	Goal Overall Progress	Objective	Category	Metric/Outcomes	Active/Deleted	Numbering
AAAA1	1	NO	BBBBBB	OE	nnnnn	Active	3.2
AAAA2	2	NO	CCCCC	OE	jjjjj	Active	3.1
AAAA3	3	YES	KKKK	BE	eee	Active	4.2
AAAA4	4	YES	LLLL	OE	rrr	Active	1.1

<tbody>
</tbody>

Thank you

 

[James006]

Hi,

It seems to me the dynamic part is in fact when you are changing the Key_EEID ....

If it is the case, an event macro could run your Sort macro as soon as you are changing anything in Column A ...

Hope this will help

 

[zinah]

Hi,

Thank you for your respond! Yes, you're right. Can you please share the suggested macro?

 

[James006]

Hi again,

In your sheet module, you could test following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 1 Then Exit Sub
If Target.Target > 1 Then Exit Sub
Dim last As Long
last = ActiveSheet.Cells(Application.Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Range("A1:H" & last).Sort [A1], xlAscending, Header:=xlYes
Range("A1").Select
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Hope this will help

 

[zinah]

I copied the macro to my sheet and when I tried to run it, it didn't show this macro in my drop-down list of macros, I even copied it to a new workbook and the same happened. Is there anything I can do to fix? I have named the range of each column if you want I can share them with you?

 

[James006]

Hi,

Proposal is an Event Macro ...

see link for an explanation :

https://www.excel-easy.com/vba/events.html#worksheet-change-event

 

[zinah]

Hi,

That's super great! thank you so much for your help and guidance.

 

[James006]

You are welcome ...