Visual Basic Programming
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
 
User Name:
Password:
Remember me
Go Back   ASP Free ForumsProgrammingVisual Basic Programming

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread ASP Free Forums Sponsor:
  #1  
Old October 7th, 2007, 11:03 AM
safetyboss safetyboss is offline
Registered User
ASP Free Newbie (0 - 499 posts)
 
Join Date: Oct 2007
Posts: 2 safetyboss User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 43 m 18 sec
Reputation Power: 0
Sorting worksheets in Excel

Hello all,
The workbook I am using has about 60 worksheets. I have found subroutines that will sort either by tab color or by worksheet name alphabetically. I would like to sort by tab color, then by name in one subroutine. Can this be accomplished?

Thanks for any help,

Steve

Reply With Quote
  #2  
Old October 20th, 2007, 11:28 AM
safetyboss safetyboss is offline
Registered User
ASP Free Newbie (0 - 499 posts)
 
Join Date: Oct 2007
Posts: 2 safetyboss User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 43 m 18 sec
Reputation Power: 0
These are the two subroutines I have found. Can they be combined or re-written into one?

Here is the first:

Sub GroupSheetsByColor()
Dim lCount As Long, lCounted As Long
Dim lShtLast As Long

lShtLast = Sheets.Count

For lCount = 1 To lShtLast
For lCounted = lCount To lShtLast
If Sheets(lCounted).Tab.ColorIndex = Sheets(lCount).Tab.ColorIndex Then
Sheets(lCounted).Move Before:=Sheets(lCount)
End If
Next lCounted
Next lCount

End Sub

Here is the second:
(This one I can select one color group at a time)

Sub SortWorksheets()
'If I sort by color first, I can then select the'
' color group and use this subroutine'
Dim N As Integer
Dim M As Integer
Dim FirstWSToSort As Integer
Dim LastWSToSort As Integer
Dim SortDescending As Boolean
Dim AscDesc

AscDesc = MsgBox(prompt:="Sort worksheets in ascending order?", _
Title:="Sort Order", _
Buttons:=vbYesNoCancel)
If AscDesc = vbYes Then
SortDescending = False
ElseIf AscDesc = vbNo Then
SortDescending = True
Else
Exit Sub
End If
If ActiveWindow.SelectedSheets.Count = 1 Then
FirstWSToSort = 1
LastWSToSort = Worksheets.Count
Else
With ActiveWindow.SelectedSheets
For N = 2 To .Count
If .Item(N - 1).Index <> .Item(N).Index - 1 Then
MsgBox "You cannot sort non-adjacent sheets"
Exit Sub
End If
Next N
FirstWSToSort = .Item(1).Index
LastWSToSort = .Item(.Count).Index
End With
End If

For M = FirstWSToSort To LastWSToSort
For N = M To LastWSToSort
If SortDescending = True Then
If UCase(Worksheets(N).Name) > UCase(Worksheets(M).Name) Then
Worksheets(N).Move Before:=Worksheets(M)
End If
Else
If UCase(Worksheets(N).Name) < UCase(Worksheets(M).Name) Then
Worksheets(N).Move Before:=Worksheets(M)
End If
End If
Next N
Next M
End Sub

Steve

Reply With Quote
Reply

Viewing: ASP Free ForumsProgrammingVisual Basic Programming > Sorting worksheets in Excel


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump



 Free IT White Papers!
 
How to Present Effectively Online
This white paper offers practical and actionable advice on the key steps that any presenter should consider as they plan and execute a Webinar or online meeting.

 
Open Source Security Myths
Open Source Software (OSS) is computer software whose source code is available to the general public with relaxed or non-existent intellectual property restrictions (or arrangement such as the public domain), and is usually developed with the input of many contributors.

 
Power and Cooling Capacity Management for Data Centers
This paper describes the principles for achieving power and cooling capacity management.

 
Scalable, Fault-Tolerant NAS for Oracle - The Next Generation
For several years NAS has been evolving as a storage alternative for Oracle databases, and for good reason: NAS is quite often the simplest, most cost-effective storage approach for Oracle. Learn about the benefits that HP's approach to scalable NAS brings to Oracle environments in this comprehensive white paper.

 
Understanding Web Application Security Challenges
This white paper discusses many common threats and preventive measures for Web application security, and explains what you can do to help protect your organization.

 

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2009 by Developer Shed. All rights reserved. DS Cluster 1 hosted by Hostway
Stay green...Green IT