Sorting sheets works okay, but how to keep 2 sheets at the front.

[StargateFanNotAtHome]

I have this macro for sorting worksheets in alphanumerical order:
*********************************************
Public Sub Sort_WORKSHEET_TABS()

Dim iCount As Integer
Dim i As Integer
Dim j As Integer

On Error Resume Next
iCount = Sheets.Count
For i = 1 To iCount - 1
For j = i + 1 To iCount
If Sheets(j).Name < Sheets(i).Name Then
Sheets(j).Move before:=Sheets(i)
End If
Next j
Next i

End Sub
*********************************************

Is there any way to make sure that sheet #1 and #2 (sheet 1 called
"General Ledger", and sheet 2 called "PA") either stay as first and
second place or get put _back_ into 1st and 2nd places?

Thank you! D

 

[Jim Cone]

'---
Next i
Sheets( "PA") .Move before:=Sheets(1)
Sheets("General Ledger").Move before:=Sheets(1)
End Sub
--
Jim Cone
Portland, Oregon USA



"StargateFanNotAtHome"
<[email protected]>
wrote in message
I have this macro for sorting worksheets in alphanumerical order:
*********************************************
Public Sub Sort_WORKSHEET_TABS()

Dim iCount As Integer
Dim i As Integer
Dim j As Integer

On Error Resume Next
iCount = Sheets.Count
For i = 1 To iCount - 1
For j = i + 1 To iCount
If Sheets(j).Name < Sheets(i).Name Then
Sheets(j).Move before:=Sheets(i)
End If
Next j
Next i

End Sub
*********************************************

Is there any way to make sure that sheet #1 and #2 (sheet 1 called
"General Ledger", and sheet 2 called "PA") either stay as first and
second place or get put _back_ into 1st and 2nd places?

Thank you! D

 

[Chip Pearson]

See the code at http://www.cpearson.com/excel/sortws.aspx . It allows
you to optionally specify the first and/or last worksheets to include
in the sort. Just set the first sheet to sort as Sheet(3) and it will
leave Sheet(1) and Sheet(2) in place.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[StargateFanNotAtHome]

Jim and Chip, thanks so very much for your responses.

Chip, yes, your page comes up repeatedly during searches. I did try
to muddle through again yesterday but must admit that I'm a very slow
learner when it comes to vb coding and found that it's still beyond me
at this stage. But now that you've pointed me to a specific spot,
I'll go back and look at your page again. It's been helpful in
explaining things so now that I know that one answer lies there, I'll
see if I can figure out your approach in the near future.

Jim, this was the easiest approach to adopt at this time. I added
your two lines to the end of my script and it seems to work
perfectly! I added a very simple error line so that if any future
user changed the name of the sheet, that it would still go ahead and
sort. Later on I may add a prompt re the changed sheet names but for
now, if any future user change the names, they'll have to deal with
putting the 2 summary sheets back manually but the sort will still
work since I tested it by renaming the first two summary sheets
slightly.

Here's the new sorting script with the sheet placement part at the
end:

*************************************
Public Sub Sort_Sheets_In_Alphanumeric_Order()

Dim iCount As Integer
Dim i As Integer
Dim j As Integer

On Error Resume Next
iCount = Sheets.Count
For i = 1 To iCount - 1
For j = i + 1 To iCount
If Sheets(j).Name < Sheets(i).Name Then
Sheets(j).Move before:=Sheets(i)
End If
Next j
Next i

On Error Resume Next
Sheets("PA").Move before:=Sheets(1)
Sheets("General Ledger").Move before:=Sheets(1)

End Sub
*************************************

Thanks much!

Every once in a long while someone asks me how I know all I know re
Excel and Word (though it's not a thimble's-worth of what you guys
know, it is a lot more than the average user I come in contact with
<g>) and I _always_ respond that it's all thanks to these ngs! You
folks make our work a whole lot easier, so a big thank you once
again! D

 

[Jim Cone]

You are welcome.

If it wasn't for John Walkenbach's books and Chip Pearson's website
I couldn't pretend to know anything about Excel.

Using the ActiveWindow.SelectedSheets property is another way
to specify which sheets will be sorted.
For example, my commercial add-in "XL Extras" sorts all sheets,
if only one sheet is selected, otherwise it sorts the selected sheets.
Also, its default sort is in numerical order so that Sheet9 comes before Sheet10.
--
Jim Cone
Portland, Oregon USA
([email protected])





"StargateFanNotAtHome"
<[email protected]>
wrote in message
Jim and Chip, thanks so very much for your responses.

Chip, yes, your page comes up repeatedly during searches. I did try
to muddle through again yesterday but must admit that I'm a very slow
learner when it comes to vb coding and found that it's still beyond me
at this stage. But now that you've pointed me to a specific spot,
I'll go back and look at your page again. It's been helpful in
explaining things so now that I know that one answer lies there, I'll
see if I can figure out your approach in the near future.

Jim, this was the easiest approach to adopt at this time. I added
your two lines to the end of my script and it seems to work
perfectly! I added a very simple error line so that if any future
user changed the name of the sheet, that it would still go ahead and
sort. Later on I may add a prompt re the changed sheet names but for
now, if any future user change the names, they'll have to deal with
putting the 2 summary sheets back manually but the sort will still
work since I tested it by renaming the first two summary sheets
slightly.

Here's the new sorting script with the sheet placement part at the
end:

*************************************
Public Sub Sort_Sheets_In_Alphanumeric_Order()

Dim iCount As Integer
Dim i As Integer
Dim j As Integer

On Error Resume Next
iCount = Sheets.Count
For i = 1 To iCount - 1
For j = i + 1 To iCount
If Sheets(j).Name < Sheets(i).Name Then
Sheets(j).Move before:=Sheets(i)
End If
Next j
Next i

On Error Resume Next
Sheets("PA").Move before:=Sheets(1)
Sheets("General Ledger").Move before:=Sheets(1)

End Sub
*************************************

Thanks much!

Every once in a long while someone asks me how I know all I know re
Excel and Word (though it's not a thimble's-worth of what you guys
know, it is a lot more than the average user I come in contact with
<g>) and I _always_ respond that it's all thanks to these ngs! You
folks make our work a whole lot easier, so a big thank you once
again! D