Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-10-2011, 06:50 AM
tswiers tswiers is offline Generic range for custom sort macro? Windows XP Generic range for custom sort macro? Office 2007
Novice
Generic range for custom sort macro?
 
Join Date: Aug 2011
Posts: 1
tswiers is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 08-11-2011, 12:35 AM
Catalin.B Catalin.B is offline Generic range for custom sort macro? Windows Vista Generic range for custom sort macro? Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

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
Reply With Quote
  #3  
Old 08-11-2011, 02:40 AM
Catalin.B Catalin.B is offline Generic range for custom sort macro? Windows Vista Generic range for custom sort macro? Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

you better remove ActiveCell. from this line

.SetRange ActiveCell.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(1042, 3))
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Generic range for custom sort macro? 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
Generic range for custom sort macro? 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

Other Forums: Access Forums

All times are GMT -7. The time now is 10:54 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft