Auto sort on open

[selkov]

Here is a macro that will sort the worksheets on opening.
Its focus is to move a particular sheet to the left most position.
The balance of the worksheests names are either numbered or numbers prefixed with the "NEW" designation.

I would like to ad code to this that would:
1] make sure the "NEW" sheet is leftmost on openeing.
2] then all sheests that read "NEW (x)" *x being numbers
3] then all numbered sheets

It is also important that I can sort the sheet (High to low) within the sun catagories.


i.E....
NEW. New 1, new 2, new 3, new 4, 12333,12334, 12335, 12336

Can anyone help?




Application.ScreenUpdating = False
'Align sheets
For Each Sheet In Application.Worksheets
Range("e1").Select
Sheet.Activate
Next
ActiveWorkbook.Sheets(1).Activate
' Move NewSheet
On Error Resume Next
ActiveWorkbook.Sheets("NEW").Move Before:=ActiveWorkbook.Sheets(1)
Err.Clear
Application.ScreenUpdating = True
ActiveWorkbook.Saved = True
End Sub

 

[erik.van.geit]

Erik,
it opened a second workbook that made a list of all worksheets in the first workbookin colum 1. It did not sort those entries nore did it reposition any worksheets in the first book.

Hi, selkov,

that is exactly what it should do
it is a portion of the original macro, where you told me it bugged
now it didn't bug I'm happy but this is also confusing ...

please retry the original macro
perhaps quote this line

'.Close False

so you can check what happened in the temporary workbook

if problems, please report in detail

best regards,
Erik

 

[selkov]

Erik
Where should I place that code?

Here is the sectionof code where thew original Macro crashes:

.Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

 

[erik.van.geit]

Erik
Where should I place that code?

Here is the sectionof code where thew original Macro crashes:

.Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

there is a line with

.Close False

the only thing I asked was to put a quote before it !

what excel are you using ?
leave out the last argument DataOption1 (I think this argument is more recent)

Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

or 2

Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False