Macro to sort a dynamic list

[alis88]

Morning all

I was wondering if someone would be kind enough to provide me with a solution to my problem.

I have a workbook which contains three worksheets.

There is an ENTRY worksheet, which contains a list, which the user uses to enter details of a new product. The user adds the product to the end of the list already there. One field which must be filled is the "Status" field, where the user selects whether the product is either on sale, or sold-out.

The other worksheets (ON SALE and SOLD-OUT) display the list of the relevant materials from the ENTRY sheet, i.e. a list of all materials sold-out, or all materials on sale, but these worksheets must be sorted in alphabetic order.

Basically, the ENTRY sheet contains all materials unordered, and the other sheets contain only relevant materials ordered.

The sort must be by material ID. It must be immediate (i.e. no need to "refresh" the workbook), on click of ON SALE or SOLD-OUT.

Can someone please help me with a macro for this?

Thanks in advance.

Alis
<!-- / message --><!-- attachments -->

 

[Yard]

I would recommend your ON SALE and SOLD-OUT sheets contain Pivot Tables which read from your ENTRY sheet.

You can use a dynamic named range to ensure the Pivot Tables always pick up all data.

You can set the Pivot Table page fields to "on sale" and "sold-out" in the relevant sheets.

You can use

Private Sub Worksheet_Activate()

ActiveSheet.PivotTables("MyPivotTable").PivotCache.Refresh

End Sub

in the code for each of the 2 report sheets to automaticaly refresh.

HTH