Prev: Totaling cells from separate worksheets to master form
Next: LAST, FIRST to First Last
From: Jim on 21 Apr 2010 17:37
I'm trying to set up a macro that can be used from multiple workbooks with
the same columns but different worksheet names. The number of rows will also
vary. How can I reference the column rather than the range? I want it to
sort by the Customer column which is the column header for column A. Below
is my current code which references a specific worksheet name and range
(which I'm trying to get away from). I recorded the macro for this code.

Columns("A:A").Select
Selection.FormatConditions.AddUniqueValues

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).DupeUnique = xlDuplicate
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("A1").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add(Range("A2:A251"), _
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color =
RGB(255, 0 _
, 0)
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range( _
"A2:A251"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:V251")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub


Thank you,
Jim
From: JLatham on 21 Apr 2010 22:21
Well, here's a kind of 'generic' or skeleton for a routine that will work on
the current active sheet and you define what columns to include in the sort.
As set up it's just sorting 1 column, but can handle several with one change,
and could handle multiple 'keys' with minor modification. It'll work with
Excel 2007 or 2003. And since I've done away with the DataOption:= entries,
it'll even work probably as far back as Excel 2000. The 2007 macro recorder
sure adds a lot of stuff that (my humble opinion) just isn't needed to just
accomplish the sort.

There are more possible variations to this than there is room for describing
them here. For example, if I wanted to run the same sort on every sheet in a
workbook, instead of referencing the ActiveSheet, I could add a little code
like this:

Dim anySheet As Worksheet

For Each anySheet in ThisWorkbook.Worksheets
'and put the setup and actual sort code in here
'that is all but the declarations
'and work through all sheets
' just change ActiveSheet. to anySheet. in the
' code below and it would work
Next ' end anySheet loop

Sub SortingSkeleton()
'change these constants as desired, if both
'first and last column to sort are the same, then
'just a single column will be sorted
'the "keyColumn" is a column within the
'sorted ones that the sort is to be sorted by
'if you're writing a 1-column sort, then
'all 3 columns would be the same
Const firstColToSort = "A"
Const lastColToSort = "A"
Const keyColumn = "A"
Const firstRowToSort = 2 ' assumes labels in row 1

Dim sortRange As Range
Dim sKey1 As Range
Dim lastRow As Long

'works on the currently selected/active sheet
lastRow = ActiveSheet.Range(keyColumn & _
Rows.Count).End(xlUp).Row
Set sortRange = ActiveSheet.Range(firstColToSort & _
firstRowToSort & ":" & lastColToSort & lastRow)
Set sKey1 = ActiveSheet.Range(keyColumn & firstRowToSort)

'you can record macros while performing other sorts,
'such as on multiple columns with 1, 2 or 3
'sort keys and modify this code to emulate them
'based on the recorded macros
sortRange.Sort Key1:=sKey1, Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

'housekeeping
Set sortRange = Nothing
Set sKey1 = Nothing
End Sub


"Jim" wrote:

> I'm trying to set up a macro that can be used from multiple workbooks with
> the same columns but different worksheet names. The number of rows will also
> vary. How can I reference the column rather than the range? I want it to
> sort by the Customer column which is the column header for column A. Below
> is my current code which references a specific worksheet name and range
> (which I'm trying to get away from). I recorded the macro for this code.
>
> Columns("A:A").Select
> Selection.FormatConditions.AddUniqueValues
>
> Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
> Selection.FormatConditions(1).DupeUnique = xlDuplicate
> With Selection.FormatConditions(1).Interior
> .PatternColorIndex = xlAutomatic
> .Color = 255
> .TintAndShade = 0
> End With
> Selection.FormatConditions(1).StopIfTrue = False
> Range("A1").Select
> ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
>
> ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add(Range("A2:A251"), _
> xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color =
> RGB(255, 0 _
> , 0)
> ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range( _
> "A2:A251"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
> xlSortTextAsNumbers
> With ActiveWorkbook.Worksheets("Sheet1").Sort
> .SetRange Range("A1:V251")
> .Header = xlYes
> .MatchCase = False
> .Orientation = xlTopToBottom
> .SortMethod = xlPinYin
> .Apply
> End With
> Range("A1").Select
> End Sub
>
>
> Thank you,
> Jim
 | 
Pages: 1
Prev: Totaling cells from separate worksheets to master form
Next: LAST, FIRST to First Last