Prev: filtering rows across several columns
Next: Banded rows all scattered after conditional formatting
From: Crickett on 23 Mar 2010 09:28
I have a workbook with multiple sheets which have the same format. I need to
sort all in the same manner. How can this be done.

I noticed that the sort function is not available when selecting/grouping
multiple sheets.
From: JLatham on 23 Mar 2010 15:06
I'm going to take you at your word and make a few assumptions. Taking you at
your word: ALL sheets in the workbook need to be sorted. Assumptions: only 1
column is used to determine the sort order, and the sort is to be in
ascending order.

The following code will do that, and allows you to define which columns are
included in the sort, and which column is the one to base the sort on. One
other column "testCol" is used to determine how far down the sheet the sort
needs to be applied to.

To add the code to your workbook, open it, press [Alt]+[F11] to get into the
VB Editor. Choose Insert --> Module from the VBE menu and copy and paste
the code below into the module, edit as required and close the VBE and give
it a test run. Naturally, you should make a backup of your file before you
test, just in case it doesn't perform as anticipated.

Sub SortAllSheets()
'this sorts each sheet in same fashion
'assumes row 1 has labels
'
'redefine these to suit your requirements
Const firstColToSort = "A"
Const lastColToSort = "F"
Const keyCol = "B" ' field to sort on
'this next should be a column that will
'always have entries in it, and can be
'same as keyCol but does not have to be.
Const testCol = "B"

Dim anyWS As Worksheet
Dim sortRange As Range
Dim sortKey As Range
'next improves performance
Application.ScreenUpdating = False
For Each anyWS In ThisWorkbook.Worksheets
Set sortRange = anyWS.Range(firstColToSort & "1:" _
& lastColToSort & _
anyWS.Range(testCol & Rows.Count).End(xlUp).Row)
Set sortKey = anyWS.Range(keyCol & 2)
sortRange.Sort Key1:=sortKey, Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Next
'just good housekeeping
Set sortRange = Nothing
Set sortKey = Nothing
Set anyWS = Nothing
End Sub

"Crickett" wrote:

> I have a workbook with multiple sheets which have the same format. I need to
> sort all in the same manner. How can this be done.
>
> I noticed that the sort function is not available when selecting/grouping
> multiple sheets.
 | 
Pages: 1
Prev: filtering rows across several columns
Next: Banded rows all scattered after conditional formatting