Sort Macro

[Big Tony]

I would like to be able to sort the same column in all worksheets in a
workbook. I found two macros that were similar in this discussion group but
have not been able to make them work. The key column in each worksheet has a
different number of rows but it is always the same column.

Thanks in advance for your assistance.

 

[Guest]

what have you done so far to make them work?
if you pasted them in the vb editor then
tools>macro>macros>select macro>run should do it.
or alt+F8>select macro>run

 

[Big Tony]

I have this macro:

Sub SortCombo()
' This works on all worksheets
Application.ScreenUpdating = False
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Select
Range("A1").Select
Range("A1:A245").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Next ws
Application.ScreenUpdating = True
End Sub

I cannot figure out how to start in specified column (say column 8) where
columns are of different length in each worksheet. I know it has something
to do with the Columns() command maybe coupled with the With command.

 

[Guest]

hi again
this macro sort column A only
range("A1").select = go to A1
range("A1:A245").sort = sort this range only
it looks like it is sort combo box contents all though i
can imagine a combo box range on every sheet that is that
big. odd.
anyway, column 8 is H so in your macro change the line
Range("A1:A245").Sort Key1:=Range("A2") to
Selection.sort Key1:=Range("H2")
that you make it work
it should sort all sheet in your file by column H.
good luck

-----Original Message-----
I have this macro:

Sub SortCombo()
' This works on all worksheets
Application.ScreenUpdating = False
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Select
Range("A1").Select
Range("A1:A245").Sort Key1:=Range("A2"),

Order1:=xlAscending, Header:= _

 

[Big Tony]

I am getting a run-time error '1004'. Application_defined or Object-defined
error. Here is the modified code.
Debugger points to the Selection.SortKey1 statement. I guess I nedd lessons
on understanding what the debugger is trying to tell me.

Sub SortCombo()
' This works on all worksheets
Application.ScreenUpdating = False
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Select
Range("G1").Select
Selection.Sort Key1:=Range("G2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Next ws
Application.ScreenUpdating = True
End Sub