#1
|
|||
|
|||
Generic range for custom sort macro?
I am trying to sort several sheets 2 columns at a time based on cell color. I have been able to create a macro to accomplish this in one sheet but when I switch to the next sheet it only sorts the original range (ex. column A & B) on the following sheets. Is there a generic range I can use instead of specific range?
Here is my macro code: Sub colorsort2() ' ' colorsort2 Macro ' ' Keyboard Shortcut: Ctrl+w ' ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear ActiveWorkbook.ActiveSheet.Sort.SortFields.Add(Act iveCell.Range( _ "A1:A1042"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.COLOR _ = RGB(0, 176, 240) With ActiveWorkbook.ActiveSheet.Sort .SetRange ActiveCell.Range("A1:B1042") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Instead of ActiveCell.Range("A......B....") Is there a generic term like offset or ("2 column:50 row") ? Just like using ActiveWorkbook.ActiveSheet.Sort instead of ActiveWorkbook.Worksheets("5-3") for example.... which allows me to perform this macro on any sheet, I would like it to perform on any two column section I have selected. |
#2
|
|||
|
|||
you can change this line to this one:
.SetRange ActiveCell.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(1042, 3)) to set the range to rows 1 to 1042, col 1 to 3, in this example but your macro works weird |
#3
|
|||
|
|||
you better remove ActiveCell. from this line
.SetRange ActiveCell.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(1042, 3)) |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Creating a generic macro? | suerose | Word | 2 | 07-04-2011 07:59 PM |
Create Custom menu using a macro | twnty2 | PowerPoint | 0 | 06-29-2011 04:26 PM |
Sort Range | boutells | Excel | 1 | 07-15-2009 03:02 AM |
Sort By Last Name in a List | eliz.bell | Word | 8 | 05-07-2009 01:33 PM |
Weird problem with generic file icons in Office 2K | bankboysb | Office | 0 | 01-23-2006 02:10 PM |