Autosort After Adding Entry

[KirkAZ]

Hi Excel Overlords,

I have a conundrum that I may have over complicated a bit. I am tracking the quantity of a product being turned in by 21 employees in order to carry out labor analysis. The sheet that references this data does so by; (Header A: Name Header B: Amt

Adam	=Adam!B36
Alec	=Alec!B36
Anita	=Anita!B36
Brigham	=Brigham!B36
Cavan	=Cavan!B36

As new data gets entered into their sheets, the finished product as well as a few other metrics are recorded. B36 represents a sum of the finished product on each one of their respective sheets.

I currently use this macro, but I believe it is best suited for direct data entry on the sheet being worked on as it does not autosort when values get added into the B column on each employee's worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Or Target.Cells.Count > 1 Then Exit Sub
Dim SortRange As Range
Set SortRange = Range(("A1"), Cells(Rows.Count, 2).End(xlUp))
SortRange.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes
End Sub


Let me know if there is any more information I can provide. Recap: I hope for data entries in worksheets Adam:Tyler! to reflect in their respective sum cell (Adam:Tyler!B36), and then for these new sums to ultimately be reflected in my summary sheet where it dynamically sorts and updates an on-running chart.

Also, any tips for someone with 0 excel macro/coding experience, but all of the drive to learn? Maybe a good starting book?

Best,
KirkAZ

 

[RudiS]

The macro you are using is an event based macro for a specific sheet. It will not trigger when changes occur on any sheet, rather for a singular specific sheet on which the event code resides. I assume it is supposed to sort the summary sheet where you are entering the name of the value in a list.

I am a bit confused as to what you actually want done?
Do you want the code to simply sort the summary sheet each time you enter a new name in it?
Please clarify.

TX

 

[ParamRay]

You need a workbook-level event rather than a worksheet-level event.

Try this one, which goes in the code module for ThisWorkbook:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

  'Exit if not a worksheet
  If Not TypeOf Sh Is Worksheet Then Exit Sub
  
  'Exit if B36 is not changed
  If Intersect(Target, Sh.Range("B36")) Is Nothing Then Exit Sub
  
  'Sort the range
  With Me.Sheets("Sheet1")  '<-- CHANGE SHEET NAME AS NEEDED
    .Range("A1").CurrentRegion.Sort Key1:=.Range("B1"), Header:=xlYes
  End With

End Sub

 

[KirkAZ]

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

  'Exit if not a worksheet
  If Not TypeOf Sh Is Worksheet Then Exit Sub
  
  'Exit if B36 is not changed
  If Intersect(Target, Sh.Range("B36")) Is Nothing Then Exit Sub
  
  'Sort the range
  With Me.Sheets("Sheet1")  '<-- CHANGE SHEET NAME AS NEEDED
    .Range("A1").CurrentRegion.Sort Key1:=.Range("B1"), Header:=xlYes
  End With

End Sub

I changed "Sheet1" to the name of the actual worksheet "Race" and saved it as a macro-enabled file. However, nothing has happened. How to I activate the module once I've added it in?

And for others, I am wanting my "Race" sheet to simply autosort smallest->largest whenever entries are made throughout Adam:Trevor!. It is autosorting a =sum(B5:B35) cell 'B36'.

Thank you for your time and efforts,
KirkAZ