Data sorted when sheet opens

**cakonopka** · 01-30-2010, 09:07 AM

Hi Folks

Just a quick one, hope its an easy one for someone.

How do i make a sheet sort a small table of data from A-Z automatically every time i open that sheet?

Any ideas.

Cheers in Advance

Chris

**mrice** · 01-30-2010, 09:18 AM

Paste the following macro into the ThisWorkbook tab in the VBA editor (Alt F11) and adjust as necessary to specify the sheet, the range of data and the column to sort by.

Private Sub Workbook_Open()
Sheets("MySheetName").Cells(1, 1).CurrentRegion.Sort Header:=xlYes, key1:=Sheets("MySheetName").Cells(1, 1), order1:=xlAscending
End Sub

**Richard Buttrey** · 01-30-2010, 09:21 AM

Hi,
I'm assuming when you refer to a sheet in your original post, you really do mean a sheet and not the workbook.

In the VBE use the Sheet_Activate event procedure and enter the following line of code

Private Sub Worksheet_Activate()
    Range("A1").CurrentRegion.Sort key1:=Range("A2"), order1:=xlAscending, header:=xlYes
End Sub

change the order:= if necessary to xlDescending, and add further sort keys if required.

If you mean when you open the workbook then use the same line of code in the Workbook_Open event.

HTH