Sort Macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Big Tony - 28 Jan 2005 13:37 GMT

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.

Reply to this Message

anonymous@discussions.microsoft.com - 28 Jan 2005 16:18 GMT

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

>-----Original Message-----
>I would like to be able to sort the same column in all worksheets in a
[quoted text clipped - 4 lines]
>Thanks in advance for your assistance.
>.

Reply to this Message

Big Tony - 28 Jan 2005 17:35 GMT

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.

> what have you done so far to make them work?
> if you pasted them in the vb editor then
[quoted text clipped - 12 lines]
> >Thanks in advance for your assistance.
> >.

Reply to this Message

anonymous@discussions.microsoft.com - 28 Jan 2005 19:50 GMT

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:
[quoted text clipped - 36 lines]
>>
>.

Reply to this Message

Big Tony - 31 Jan 2005 13:17 GMT

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

> hi again
> this macro sort column A only
[quoted text clipped - 57 lines]
> >>
> >.

Reply to this Message