Auto sort on open

selkov

Well-known Member
Joined
Jan 26, 2004
Messages
781
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, selkov,

try this
Code:
Option Explicit

Sub sort_sheets()
'Erik Van Geit
'060906
'sheets with "NEW" first

Dim sh As Worksheet
Dim arr As Variant
Dim i As Integer
Dim shName As String

Application.ScreenUpdating = False

    With Workbooks.Add
        With .Sheets(1)
        .Cells(1, 1) = "header"
            For Each sh In ThisWorkbook.Worksheets
            .Cells(Rows.Count, 1).End(xlUp)(2) = sh.Name
            Next sh
            
            With .Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
            .Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
            .AutoFilter Field:=1, Criteria1:="=*new*", Operator:=xlAnd
            .Offset(1, 0).Resize(.Rows.Count - 1, 1).Copy Cells(1, 2)
            .AutoFilter Field:=1, Criteria1:="<>*new*", Operator:=xlAnd
            .Offset(1, 0).Resize(.Rows.Count - 1, 1).Copy Cells(Rows.Count, 2).End(xlUp)(2)
            End With
        .ShowAllData
        arr = .Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp))
        End With
    .Close False
    End With
    
    With ThisWorkbook
        For i = 1 To UBound(arr) - 1
        shName = arr(i, 1)
        .Sheets(shName).Move before:=Sheets(i)
        Next i
    End With
    
Application.ScreenUpdating = True

End Sub
kind regards,
Erik
 

selkov

Well-known Member
Joined
Jan 26, 2004
Messages
781
Erik,
Sorry this does not work. In fact it does not run. It gives me an error in the first with statement. Also it is openeing another workbook & begins to print to it .
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Erik,
Sorry this does not work. In fact it does not run. It gives me an error in the first with statement. Also it is openeing another workbook & begins to print to it .
sorry, but talking about printing with regards to my code doesn't make any sense
can you show one line with PRINT in this code ??
you must have some add-in installed or your code has some events ??

some tests.....
1.
open new workbook
does it print ?

2.
record a macro opening a new workbook
stop recording
you will get
Code:
Workbooks.Add
run that macro
shouldn't be any problem

3.
now run this
Code:
Sub test()
    With Workbooks.Add
    .Close False
    End With
End Sub

problems ?

kind regards,
Erik
 

selkov

Well-known Member
Joined
Jan 26, 2004
Messages
781

ADVERTISEMENT

Sorry, Mispoke myself.
I meant to say that it openeda nother workbook & made entries into the cells.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
I meant to say that it openeda nother workbook & made entries into the cells.
It gives me an error in the first with statement
this is somehow confusing
if there is an error in the first WITH how come you have a ne workbook which is opened ?

and YES, it's normal a new workbook is opened to perform some sorting
afterwards it's closed again and the array (arr = .Range(Cells(1, 2... )
is used to sort the sheets

what's the problem now ?
can you clarify ?

best regards,
Erik
 

selkov

Well-known Member
Joined
Jan 26, 2004
Messages
781

ADVERTISEMENT

Sorry I do not know that much to be of help.
It does open the new workbook.
It enters two entires r1c1 r2c1.
then gives me an error.
Run time error 1004, application defined or object defined error.
 

selkov

Well-known Member
Joined
Jan 26, 2004
Messages
781
Sorry I do not know that much to be of help.
It does open the new workbook.
It enters two entires r1c1 r2c1.
then gives me an error.
Run time error 1004, application defined or object defined error.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
run this and report results please
Code:
Option Explicit

Sub test()
    
    Dim sh As Worksheet

    With Workbooks.Add
        With .Sheets(1)
        .Cells(1, 1) = "header"
        .Cells(1, 2) = "worksheetscount"
        .Cells(2, 2) = Worksheets.Count
        
        On Error Resume Next
            For Each sh In ThisWorkbook.Worksheets
            .Cells(Rows.Count, 1).End(xlUp)(2) = sh.Name
            Next sh

        End With
    End With

End Sub
do you see something unexpected ? is the list complete ?
run code also without "On Error Resume Next"
 

selkov

Well-known Member
Joined
Jan 26, 2004
Messages
781
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.
 

Forum statistics

Threads
1,141,850
Messages
5,708,982
Members
421,602
Latest member
jkpce1880

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top