Sort column ascending on many worksheets

T

tkincaid

i have over 100 worksheets and it will grow, they all have the same columns.
I want to make a macro that will sort each worksheet via column B with dates.

A B C

all 12/8/08 all-t-08-e-001

The columns continue until H and go to line 98.


Thank You
 
P

Per Jessen

Hi

This macro will sort all sheets based on column B, assuming you have
headings in row 1.

Sub SortSheets()
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Sheets
Columns("A:H").Sort Key1:=Range("B1"), Order1:=xlAscending,
Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Next
Application.ScreenUpdating = True
End Sub

Regards,
Per
 
O

Otto Moehrbach

Do you want every sheet sorted or are there some sheets that you want
skipped? HTH Otto
 
J

Joel

For Each sht In Sheets

sht.Cells.Sort _
Key1:=sht.Range("B1"), _
Order1:=xlAscending, _
Header:=xlGuess

Next sht
 
T

tkincaid

I copied this and it didn't work, and I do have headings in row 1 all my
information starts on line 3 column a all the way to J 98, but I want to sort
via column B which is the date.

Sorry for the post, it told me it didn't send, but it did.
 
P

Per Jessen

Try this:

Sub SortSheets()
Dim sh
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Sheets
Range("A3:J98").Sort Key1:=Range("B3"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Next
Application.ScreenUpdating = True
End Sub

Regards,
Per
 
T

tkincaid

this works, but only if I am on the sheet, I want to run a macro after all
data is entered and run it once to do all sheets.

thanks
 
P

Per Jessen

My fault :-(

This will sort all sheets at once.

Sub SortSheets()
Dim sh
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Sheets
With sh
.Range("A3:J98").Sort Key1:=.Range("B3"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End With
Next
Application.ScreenUpdating = True
End Sub

Regards,
Per
 
T

tkincaid

thank you so much for your help, I don't know what I would do without this
forum.

thanks
 
Top