A macro to sort a dynamic list

If you're just asking for those two sheets to be sorted, the code below may help. It needs to go into the ThisWorkbook module (right-click on the Excel icon to the left of the File menu and select View Code):

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

If Sh.Name = "SOLD-OUT" Or Sh.Name = "ON SALE" Then _
    Sh.Range("A1").CurrentRegion.Sort key1:=Sh.Range("A1"), header:=xlGuess

End Sub

Hello Stephen

Thank you for your reply. I pasted the code as you said, but nothing appeared to happen.

Can you help further please?

Alis

Not sure, see attachment. Click on one of the last two sheets and the data should be sorted.

I need it to pick up the data in the ENTRY sheet, so what would happen is that in the SOLD-OUT sheet, it would have:

Material ID: ALIS004, ALIS005, plus the name of the product.

In the ON SALE sheet, it would have:

Material ID: ALIS001, ALIS002, ALIS003, plus the name of the product.

i.e. it would be filtered and sorted into appropriate worksheets.

Is that any clearer to you?

Many thanks for your help.

Alis

OK, try this. It's activate automatically by the sheet, but might be better as a button.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Dim strCriteria As String

Application.ScreenUpdating = False

If Sh.Name = "SOLD-OUT" Then
    strCriteria = "Sold Out"
ElseIf Sh.Name = "ON SALE" Then
    strCriteria = "On Sale"
End If

If Sh.Name = "SOLD-OUT" Or Sh.Name = "ON SALE" Then
    Sh.UsedRange.Clear
    With Sheets("ENTRY")
        .Range("A1").CurrentRegion.Sort key1:=.Range("A1"), header:=xlGuess
        .Range("A1").AutoFilter field:=3, Criteria1:=strCriteria
        .AutoFilter.Range.Resize(, 2).Copy Sh.Range("A1")
        .AutoFilterMode = False
    End With
End If

Application.ScreenUpdating = True

End Sub