Macro Sort (VBA?)

[Paul T]

Hi All

Trying to run a macro for a sort.

If fixed column - not a problem, but I want the macro to sort if column
month tally's with B1

E.g. if Month 1-3-06 then sorts column D if 1-4-06 sorts column E etc.

I have created a little formula that high-lights the current month - i.e.
=IF($B$1=D5,"COLUMN /\","") if that helps?


A B C D
E
Enter Current Month 01/03/2006 Start
THEN CLICK BELOW RUN SORT ROUTINE
COLUMN/\
HELP Point
Name BRANCH 01/02/2006 01/03/2006 01/04/2006

This is Code if FIXED COLUMN.

Rows("5:81").Select
Application.CutCopyMode = False
Selection.sort Key1:=Range("G5"), Order1:=xlDescending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

Any help appreciated.

Paul T

 

[Dave Peterson]

If you base it on the month of the current date, your macro could just use that:

Option Explicit
sub sortMe()

with activesheet
with .rows("5:81")
.sort Key1:=.columns(month(date)), Order1:=xlDescending, _
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
end with
end with
End Sub

Although, I don't like to use xlGuess in the code--I know if row 5 is a header,
so I'd use xlno or xlyes.

Hi All

Trying to run a macro for a sort.

If fixed column - not a problem, but I want the macro to sort if column
month tally's with B1

E.g. if Month 1-3-06 then sorts column D if 1-4-06 sorts column E etc.

I have created a little formula that high-lights the current month - i.e.
=IF($B$1=D5,"COLUMN /\","") if that helps?

A B C D
E
Enter Current Month 01/03/2006 Start
THEN CLICK BELOW RUN SORT ROUTINE
COLUMN/\
HELP Point
Name BRANCH 01/02/2006 01/03/2006 01/04/2006

This is Code if FIXED COLUMN.

Rows("5:81").Select
Application.CutCopyMode = False
Selection.sort Key1:=Range("G5"), Order1:=xlDescending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

Any help appreciated.

Paul T

[Image]