multiple worksheet sort in excel 2000

[Ron]

How do I sort multiple worksheets containing columns with
similar data all at the same time? for example; if I have
10 worksheets in a workbook with the column to be sorted
containing the same type of data, I would like to sort the
same column across all worksheets. Since my data was too
large for one worksheet I had to save it with formatting
resulting in multiple worksheets. I am using excel 2000.

 

[Dave Peterson]

Each sheet is sorted separately?

If yes, I'd record a macro when I did it manually, then modify it to do the
other sheets.

For instance, I recorded this macro:
Option Explicit
Sub Macro1()
Range("A3:K23").Select
Selection.Sort Key1:=Range("C3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

I could generalize it a bit:

Option Explicit
Sub Macro1A()
Dim Wks As Worksheet
For Each Wks In ActiveWorkbook.Worksheets
With Wks
With .Range("a3:K" & .Cells(.Rows.Count, "A").End(xlUp).Row)
.Cells.Sort key1:=.Columns(3), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False
End With
End With
Next Wks
End Sub


I used column A to determine my last row. I changed the header to xlYes. And I
sorted by the 3 column in the range (C in my case).

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

If you have trouble, post your code (not the workbook). I'm sure you'll get a
reply.

 

[Ron]

Thanks for your reply. I wanted to sort across all the
sheets since I saved a large amount of data to excel
exceeding the row limit for one sheet. I wound up with
about 10 sheets.

-----Original Message-----
Each sheet is sorted separately?

If yes, I'd record a macro when I did it manually, then modify it to do the
other sheets.

For instance, I recorded this macro:
Option Explicit
Sub Macro1()
Range("A3:K23").Select
Selection.Sort Key1:=Range("C3"),

Order1:=xlAscending, Header:=xlGuess, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

I could generalize it a bit:

Option Explicit
Sub Macro1A()
Dim Wks As Worksheet
For Each Wks In ActiveWorkbook.Worksheets
With Wks
With .Range("a3:K" & .Cells

(.Rows.Count, "A").End(xlUp).Row)

 

[Dave Peterson]

I'm not sure excel is the best tool for this. (Even if I knew how (I don't), I
wonder how long it would take to sort 65k*10 rows.)

I've never used Access, but you may find that easier to sort massive amounts of
data.

And I've seen 3rd party text file sorters. You could search the web if that's
an option.