Sorting worksheet Tabs

[JD]

I have a worksheet with many tabs. I have named each tab
something unique. I would like to alphabetize them
automatically. Does anyone know if this is possibls? Also
what is the maximum number of tabs I can have in any
workbook?

 

[Tom Ogilvy]

See Chip Pearson's page that has VBA code to do this:

http://www.cpearson.com/excel/sortws.htm

 

[Robert Christie]

Hi Tom

Thankyou for the tip to Chip Pearson's page

Following on from JD's post, when opening a new sheet in
a workbook either from the menu or in a macro, the new
sheet always appears to the left of the active sheet.
Is their a reason for this?
Can it be changed to the right of active sheet as a Excel
customizing setup?
or
Can it be changed to the right of active sheet, if
opening a new sheet via Excel macro code?.

TIA

Bob Christie

 

[Tom Ogilvy]

As a setting, I don't believe so
for code

Worksheets.Add after:=Activesheet

 

[JMay]

Tom, Below From Help!
Occurs when a new sheet is created in any open workbook
Example
This example moves the new sheet to the end of the workbook.

Private Sub App_WorkbookNewSheet(ByVal Wb As Workbook, _
ByVal Sh As Object)
Sh.Move After:=Wb.Sheets(Wb.Sheets.Count)
End Sub

When I paste this into ThisWorkbook
and then
With sheet2 active I do menu Insert Worksheet
Sheet4 gets created to the left of sheet2

I seem to always miss the first train that leaves the station...
Can you assist?
JMay

 

[Chip Pearson]

You need to declare the App variable and initialize it to
Application. E.g.,


Dim WithEvents App As Application
Private Sub App_WorkbookNewSheet(ByVal Wb As Workbook, _
ByVal Sh As Object)
Sh.Move After:=Wb.Sheets(Wb.Sheets.Count)
End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Tom Ogilvy]

Chip showed you how to instantiate Application Level events. If you want it
to be a workbook level event (thisworkbook module) then it would be:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Sh.Move After:=ThisWorkbook.Sheets( _
ThisWorkbook.Sheets.Count)
End Sub

 

[JMay]

Thks Chip - I got it working...

You need to declare the App variable and initialize it to
Application. E.g.,


Dim WithEvents App As Application
Private Sub App_WorkbookNewSheet(ByVal Wb As Workbook, _
ByVal Sh As Object)
Sh.Move After:=Wb.Sheets(Wb.Sheets.Count)
End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[JMay]

Got it Tom;
The Train has left the station.
Tks,
JMay