Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 23rd 10, 02:28 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2010
Posts: 1
Default How to sort multiple sheets (which have same format) at one time?

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.

  #2   Report Post  
Old March 23rd 10, 08:06 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,204
Default How to sort multiple sheets (which have same format) at one time?

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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
SORT MULTIPLE COLUMNS AT SAME TIME automatically bkunes Excel Worksheet Functions 7 February 21st 08 06:05 PM
Unhiding multiple sheets at a time -maverick- Excel Discussion (Misc queries) 2 February 12th 08 05:07 PM
How do I protect multiple sheets at one time? Kelly Excel Worksheet Functions 1 February 27th 06 05:30 PM
How do I data sort multiple sheets in Excel that are linked with i yojlem Excel Worksheet Functions 1 November 30th 05 09:55 PM
CHANGE SET-UP ON MULTIPLE SHEETS AT 1 TIME DEMONET48 Excel Discussion (Misc queries) 2 January 14th 05 09:37 PM


All times are GMT +1. The time now is 10:26 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017