Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / January 2008

Tip: Looking for answers? Try searching our database.

Sort all worksheet contents with a macro

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Scott - 11 Dec 2005 22:32 GMT
I have searched the boards and have not found what I need.  Hoping you can
help.  I am using the following to sort worksheet order within a workbook and
it is working perfectly.  

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

What I still haven't found is how to re-sort (by first column) the contents
of all worksheets.  Idea situation would be to combine this with the item
referenced above.

Thanks in advance.

Scott
Tom Ogilvy - 11 Dec 2005 22:39 GMT
Sub SortWorksheets()

Dim N As Integer
Dim M As Integer
Dim FirstWSToSort As Integer
Dim LastWSToSort As Integer
Dim SortDescending As Boolean
Dim sh as Worksheet

SortDescending = False

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
for each sh in Thisworkbook.Worksheets
  sh.UsedRange.Sort Key1:=sh.Range("A1"), _
      Order1:=xlAscending, Header:=xlYes
Next
End Sub

Adjust the header setting to fit your data.

Signature

Regards,
Tom Ogilvy

> I have searched the boards and have not found what I need.  Hoping you can
> help.  I am using the following to sort worksheet order within a workbook and
[quoted text clipped - 9 lines]
>
> Scott
Scott - 12 Dec 2005 13:58 GMT
Worked like a charm.  Thanks Tom!
Ren - 29 Jan 2008 03:32 GMT
Hi Tom,

i have multiple sheets from A to Z
and i need to sort column L
i used this code

Sub sort()
cells.sort Key1:=range("L2"), Header:=xlYes
End Sub

it's working for the single sheet.
but i want it for all sheets.
how to modify your code.i tried but could't get thru.

thanks in advance
Ren

> Sub SortWorksheets()
>
[quoted text clipped - 61 lines]
> >
> > Scott
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.